Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rolling 52 week sales and preceding 52 week sales

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.

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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?

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.