Forum Discussion
13 Week Average - prior year
Hello,
I am trying to create a measure that will show the 13 week average bid count for the prior year, but up to the same period as the current year. The business also asked to show the prior fiscal week rather than up to the current week. I was able to successfully create this 13 week average bid count for the current year, but cannot seem to figure out how to translate this DAX expression to show the prior year same period. Here is the dax below:
Please let me know if you have any suggestions.
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 13 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]),[Distinct bid count (SF)]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
1 Reply
- amitchandak
Super User
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 13 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]),[Distinct bid count (SF)]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))