Display Last X Week Data Dynamically Based On 'To' Date Selection

[Anonymous]

Hi everyone,

We have requirement in our project such that based on the 'To' date selected in the period selector, we need to show last 4 weeks data alone in the chart. Supposing we are at the 47th Week, then the chart should display only values for 44 to 47th week. If the 'To' date selected falls under 44th week, the data displayed should be for  41st to 44th week

We have the start and end date of every week defined in the Cube as well. We are following Gregorian Week (Mon-Sun). 

I had created a couple of DAX Measures which will calculate sum of last 4 weeks based on the MAX of Date selected, as shown below:

Last 4 Week Sales = CALCULATE(SUM(Data[Sales]), FILTER(ALL(Data[Week Number]), Data[Week Number] > MAX(Data[Week Number]) - 3 && Data[Week Number] < MAX(Data[Week Number])))

Last 4 Week Sales 2 = VAR MaxDateSelected =

CALCULATE ( MAX ( Data[Date] ), ALL ( Data) )

VAR Week_of_To_Date = WEEKNUM([Last Working Day of Week],2)

VAR Edate = Week_of_To_Date - 3

RETURN

IF (

MaxDateSelected <= MAX ( Data[Date]) && MaxDateSelected > Week_of_To_Date,CALCULATE ( SUM ( Data[Sales] ), ALL ( Data) ))

Last Working Day of Week =

VAR TodaysDate = MAX(Data[Date])

VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekNum1",WEEKNUM([Date]))

VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],3))

VAR TodaysWeekNum = WEEKNUM(TodaysDate)

VAR Calendar4 = FILTER(Calendar3,[WeekNum1]=TodaysWeekNum&&[WeekDay1]<6)

VAR FirstWorkingDay = MAXX(Calendar4,[Date])

RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy")

But this DAX still shows data for all weeks, not just the last 4 weeks based on To date selection. I believe I am closer to the solution but if my approach is wrong, please correct the same.

Note: Sunday would not be having data in reality for my project, so I had declared the end of the week as a Saturday.

Thanks and regards,

BRP

[edhans · Accepted solution]

Ideally you would filter this data out before it gets loaded into Power BI via the Power Query engine, but you mentioned that your data is coming from a cube, so if it is SSAS, then you cannot use Power Query.

You have two possible option (there are probably a dozen ways to go about it, but these two are straightforward):

1. Use a calculated column to define how many weeks ago a date is, then add that column to a filter (report, page, or visual level) and only show those that are <=4. This would calculate that value:

   Weeks Ago = WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2)

2. Create a calculated table that just had the relevant data, then use that table in your visual. This measure/table would do that:

   4 Week History = 
   CALCULATETABLE(
       Sales,
       FILTER(Sales,WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2) <= 4)
   )