Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |