cancel
Showing results for
Did you mean:
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
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
3 REPLIES 3
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?

Super User

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

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors