Saturday, April 22, 2023

How calculate Monthly Average Sales based on Working Days using DAX in Power BI

How calculate  Average Monthly Sales based on Working Days using DAX in Power BI
Scenario:
Suppose we have a Data Model as follows:


The Relations are as follows:

From the above Model, we need to calculate Average Monthly Sales based on Working Days. 

To achieve this Scenario, we need to create a Working Days column in the Date table, which will return the value as 0 for Sat and Sun days, and for the remaining days it returns as 1.

WorkDayFlag = IF (tbl_Calendar[Week_Day] IN {1,7},0,1)

Next we need to create the below Measures to fulfill the above Scenario.

Net Sales = SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))

Monthly Cum Sales =
            CALCULATE([Net Sales],DATESMTD(tbl_Calendar[cDate]))

Total Working Days = SUM(tbl_Calendar[WorkDayFlag])

The below Measure returns the Cumulative Total Working Days in a Month, where the Sales exists in the facts tables.

Monthly Cum Work Days =
CALCULATE([Total Working Days],DATESMTD(tbl_Calendar[cDate]),
        FILTER(ALL(tbl_Calendar[cDate]), [Net Sales]<>BLANK()))

The below Measure returns the Monthly Average Sales based on the Working Days passed as of that Day in the Month.

Monthly Avg Sales =
VAR vCumWorkDays =
    CALCULATE([Total Working Days],DATESMTD(tbl_Calendar[cDate]),
        FILTER(ALL(tbl_Calendar[cDate]), [Net Sales]<>BLANK()))
RETURN
DIVIDE( [Net Sales], vCumWorkDays)

The below Measure returns the Monthly Average Cumulative Sales based on Working Days passed as of that Day in the Month.

Monthly Avg Cum Sales = DIVIDE( [Monthly Cum Sales], [Monthly Cum Work Days])

The result of all the Measures is as follows:


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog