Last 8 Weeks Sales spanning previous year

[Anonymous]

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 2019. I have gone through many posts but it is still not addressing my issue. I have a column for week in my calendar. My week number ends in 52 of every of every year except leap year. 

Kindly assist.

[PaulDBrown · Accepted solution]

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

[foyadeyi]

@PaulDBrown How I do modify the above to cater for a Calender Week range filter instead of just selecting a Week? And I also notice the measure above includes the current week selected as part of the preceding weeks

[PaulDBrown]

By "Calendar week range" do you mean you are selecting a range of weeks? If so, what is the number of preceding weeks you wish to show and which week is the reference to select the preceding weeks?

Can you show a mockuo of what you are trying to acieve?

[foyadeyi]

Hi @PaulDBrown, Yes, I mean a range of weeks. I have attached a screenshot. In this case, I would like to see the preceding 52 weeks which means (CW 1-3 of 2023 + 4-52 of 2022 --  this what makes the previous 52 weeks of the current selection). The calculation should starts from the week before the selected week and then backward which means from CW 3 2023 backwards. 

[PaulDBrown]

Using the model with the YearWeek table, you can achieve this by calculating the maximum YearWeek index and set the measure based on this (note the slicer are form the YearWeek table and the visual is from the calendar table):

Prev. 8 week Forecast range =
VAR MinWeekSel =
    MAXX ( ALLSELECTED ( 'Year Week Table' ), 'Year Week Table'[YW Index] ) //Selects the max YearWeek index in the range
RETURN
    CALCULATE (
        [Sum Forecast],
        FILTER (
            ALL ( 'Year Week Table' ),
            'Year Week Table'[YW Index] <= MinWeekSel + 8
                && 'Year Week Table'[YW Index] > MinWeekSel
        )
    )

Sample PBIX file attached

[V-lianl-msft]

Hi @Anonymous ,

You need to combine "year" and "week" into a new column, and then use rankx to sort the column.

Please try DAX like this:

sum_Sales_last_8_weeks =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        Sales,
        Sales[Date] <= MAX ( DimDate[Date] )
            && Sales[Date]
                > MAXX (
                    FILTER ( DimDate, DimDate[rank] = SELECTEDVALUE ( DimDate[rank] ) - 8 ),
                    DimDate[Date]
                )
    )

For more details,please refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft ,

Thank you very much, this should work however the SELECTEDVALUE in the DAX is referencing the ranked week and not country week which is 1-52.

This is where I really need help, I want user to see only country week 1-52 of any year in the report slicer and be able to get for example past 8 weeks sales even if spilled to previous year.

How can DAX be written such that ranked week can reference the selected country week or any other way to achieve this?

Regards,

Kunle

[amitchandak]

Check this file, I have created the last 12 weeks of sales using Rank. The same way you should able to get others.

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Anonymous]

@amitchandak Thank you for sharing however, my report is dynamic meaning it works by the selection on a slicer and that is where my challenge is. The week on the slicer is from 1 - 52 which can also rep any year. I am looking a formula that will work based on any selection made.

[amitchandak]

As long as all the date related information is in Date table. The formula should respond to the date on top or date related stuff on top.

[Anonymous]

Thank you @amitchandak but that is the assistance that I need- a DAX formula that can address by request. I suppose a loop like in programming will wok but I dont know how to go about it or any other way.

Thank you.

[amitchandak]

What do you mean by loop like programming?

If you need last 8 weeks, 13 weeks, or any number of the week. You need to have date table. You need to define your week. Create a Rank on week start date so it can work across years.

Last 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[Anonymous]

@amitchandak Thank you again. However, like I said, there is a slicer selection for week(1-52). The internal formula is working based on week Rank. If a particular week is selected as starting point, how does it relate back to the Ranked Week in the DAX formula. The expected DAX formula should be based on selected week from the slicer. Can you please help with that or I am still not clear.