Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Trailing 13 week total & Previous year

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])))

 

 

Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?