Scenario:
Suppose we have a Data Model 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.
Monthly Cum Sales =
CALCULATE([Net Sales],DATESMTD(tbl_Calendar[cDate]))
Total Working Days = SUM(tbl_Calendar[WorkDayFlag])
Monthly Cum Work Days =
CALCULATE([Total Working Days],DATESMTD(tbl_Calendar[cDate]),
FILTER(ALL(tbl_Calendar[cDate]), [Net Sales]<>BLANK()))
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:
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.