Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
137 | |
70 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |