Rolling 52 week sales and preceding 52 week sales

[Anonymous]

Hi all. I would like a measure for calulating latest 52 weeks sales. My data is in 4 week time periods so this would equate to 13 periods. Additionally I would like another measure that shows the period 52 weeks before the latest. Please can anyone help? I've got a sample excel file, but not sure how to attach it here.

[TomMartens · Accepted solution]

Hey,

I recommend that you start reading this article, as it provides a deep introduction in almost any date related calculation:

https://www.daxpatterns.com/time-patterns/

Based on this I add a calculated column to my Calendar table that indicates the start of the month, for this I use this DAX statement:

weekStart = 
var dayOfWeek = WEEKDAY([Date],2)
return
'Calendar'[Date] - dayOfWeek + 1

then I create a running Month Index column, this column numbers the weeks available in your calendar table:

runningMonthIndex = 
var MinWeekStart = min('Calendar'[weekStart]) 
var weekNumber = ceiling((DATEDIFF(MinWeekStart , [Date] , DAY) + 1) / 7 , 1)
return
weekNumber

This allows to easily identify the previous 52 weeks like so

Measure = 
var theMaxWeekNumber = MINX(VALUES('Calendar'[runningMonthIndex]), [runningMonthIndex])
var theCurrentStart = theMaxWeekNumber - 52
var thePrevEnd = theCurrentStart - 1
var thePrevStart = thePrevEnd - 52 + 1
return
CALCULATE(
    max('Calendar'[Date])
    ,FILTER(
        ALL('Calendar')
        , 'Calendar'[runningMonthIndex] >= thePrevStart && 'Calendar'[runningMonthIndex] <= thePrevEnd
    )
)

This example relies on ISO weeknumbering, this may has to be adjusted, but nevertheless, I guess it's a good idea to number the weeks.

Hopefully this provides some ideas.

Regards,

Tom

[Anonymous]

Hello

thanks for this. I'm trying to get a decent Calendar table to allow this to work properly. Could you please share one if you have a good one to share?

[TomMartens]

Hey,

the people from sqlbi.com provided a template that is available here: https://github.com/sql-bi/DaxDateTemplate

This template has a very extenisve description that can be found here: https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

Hopefully this provides what you are looking for.

Regards,

Tom