Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Last 8 Weeks Sales spanning previous year

Dear all,   I am struggling with a report that gives total of last 8 weeks sales based on selected Year and Week. For example, if we select week 2 of 2020, last 8 weeks sales will extend back to 20...
  • PaulDBrown's avatar
    6 years ago

    Anonymous 

    EDIT: I've added comments below showing how to follow this method but without creating a YearWeek Table

    One way of doing this is to create a YearWeek Table, linked to you calendar table through a common YearWeek field (unique values in the YearWeek Table), including and index column (I've ranked it from most recent backwards, so today's month is ranked 1, last month 2....(You then use this index column in your calculations)

     

    and the model looks like this:

     

    You can then use the fields from this YearWeek table in measures, slicers, visuals filters...

    To calculate the last 8 weeks based on the selections in the slicers:

     

     

    Prev. 8 week Forecast = CALCULATE([Sum Forecast];
                            FILTER(ALL('Year Week Table');
                            'Year Week Table'[YW Index]< SELECTEDVALUE('Year Week Table'[YW Index])+8
                            &&
                            'Year Week Table'[YW Index] >= SELECTEDVALUE('Year Week Table'[YW Index])))

     

     

    So basically you are using the index to establish the 8 week limits (8 weeks previous would be YW Index + 7)

     

    And this method gets you this:

     

    or 

     

    Hope this helps.

     

    EDIT: you can actually follow this method without having to create a new table. All you need is the the YearWeek number and YearWeek index. You can include these with  calculated columns in your calendar table using:

    YearWeek = Calendar [Year] * 100 + Calendar [Month]
    YearWeek Index = RANKX('Calendar'; 'Calendar'[YearWeek];;DESC;Dense)