Forum Discussion
Last 8 Weeks Sales spanning previous year
- 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)
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?
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.
- PaulDBrown3 years ago
Community Champion
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