Saturday, March 16, 2019

How to calculate Previous Weeks Actual based on Current Week Selection in Power BI

How to Calculate Previous Weeks data based on Slicer Selection of Current Week in Power BI
Scenario :
Suppose we have a data Model as follows..

Based on this Model, We can develop a Report that shows the data for Previous 3 Weeks based on the Current Week , selected from the WeekNum slicer, as shown below:

Note :
In my report, I have used the less data so that we are seeing most of the Blank/null values.

Slicers Used :
In the Report we have used the below 3 Slicers, from related Calendar table :
FiscalQtr : It will have the Fiscal Quarter values (eg: FY14Q1,FY14Q2..etc)
MonthName : It will have respective Months of the respective  Filtered Quarter Context.
WeekNum : It usually contains Week Numbers like 1,2,3....51,52,53., shows the respective Week Numbers based on the Filtered context.

Measures used for Cards Labels:
In the Report we have used the Cards to show 4 Weeks numbers as Labels defined using the below DAX Expressions.
Cur Week = CALCULATE(SELECTEDVALUE('Calendar'[WeekNum]))
Prev Week1 =
[Cur Week]-1
Prev Week2 =
[Cur Week]-2
Prev Week2 =
[Cur Week]-3
Note :
The card 'Cur Week' is the measure used for storing the Current Week number which we selected from the Slicer WeekNum.

Measures used for calculating Weekly Actual :
TotalSales = CALCULATE(SUMX(FactSales, FactSales[Units_Sold]*RELATED(DimProducts[Unit_Price])))

Cur Wk Act = IF(ISBLANK(CALCULATE([TotalSales],
FILTER(ALL('Calendar'),'Calendar'[WeekNum]=SELECTEDVALUE('Calendar'[WeekNum])))),
0, CALCULATE([TotalSales],
FILTER(ALL('Calendar'),'Calendar'[WeekNum]=SELECTEDVALUE('Calendar'[WeekNum]))))


Prev Wk1 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-1 && 'Calendar'[Year]=CurYear
                      )))

Prev Wk2 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-2 && 'Calendar'[Year]=CurYear
                      )))

Prev Wk3 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-3 && 'Calendar'[Year]=CurYear
                      )))

Notes :
The Measure 'Cur Wk Act' will calculates the Actuals/Bookings of the Week that we selected in the Slicer WeekNum.
In this Measure we have used the IF(ISNULL()) Functions to replace the Nulls by Zeros.
In other Measures we have not used the IF(ISNULL()) Functions, hence those columns showing the Blank values.
In all the above Measures we considered [Year], to make sure the Previous Week will be calculated correctly when we are in Week1 (eg: FY13WK53 vs FY14WK1).

-------------------------------------------------------------------------------------------------------- 
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.