Forum Discussion
Measure for Previous Week Based on Index
MightyMicrobe , refer to my blog , how to deal with week
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Using
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
amitchandak Thank you for your help, this formula works.
I have a further question if you don't mind. I'm trying to use this formula (the previous week results) in a KPI visualisation. The report has some slicers/filters. They apply to the main data label of the visualization, but not to the previous week's results based on your formula.
Can you give a hint on how to modify the formula so that it references the filters on the page or at the viz level?
Total User Previous Period =
CALCULATE([Total Users],
FILTER(ALL('Asset Customer Activity'), 'Asset Customer Activity'[Weeks Back]=MIN('Asset Customer Activity'[Weeks Back])+1))