Trailing 13 week total & Previous year

[Anonymous]

Hello everyone,

I am looking to get the average of a trailing 13 week total sales, and the same thing except previous year, same dates. If anyone has a dax function that would be great! I do have a calendar table that has a ton of useful columns (see below). ISO Weeknumber is the weeknumber associated with the date.

I do have this dax function, however, it's not giving me a rolling 13 and obviously not the average either.

Last 13 week Sales = CALCULATE(SUM('Weekly Information'[Total Sales(Column)]),FILTER(all('Calendar'),'Calendar'[ISO Weeknumber]>=min('Calendar'[ISO Weeknumber])-13 && 'Calendar'[ISO Weeknumber]<=max('Calendar'[ISO Weeknumber])))

[amitchandak · Accepted solution]

@Anonymous , Create a week rank on week start date or year week

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)    
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense)    //YYYYWW format

then you can have measures like

Last 13 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

@amitchandak How do I get previous year to date trailing 13? I thought my DAX would work, but it doesn't.

Here is the dax I used to get the average trailing 13 weeks:

TTW = CALCULATE(sum('Table'[QTY]), FILTER(ALL('Calendar'),'Calendar'[Week Rank]>=max('Calendar'[Week Rank])-12 && 'Calendar'[Week Rank]<=max('Calendar'[Week Rank])))/13

Then I used another DAX which gave me the right amounts other than this week. Instead of this week's previous year amount, it gave me next week's previous year amount as this week's amount. Here is the DAX I used for that.

Prior TTW = CALCULATE([TTW],SAMEPERIODLASTYEAR('Calendar'[Date]))

I'm trying to make it so I don't have a slicer on the report page. Any Advice?