cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Calculating rolling 52 weeks

Hi,

I try to find a rolling 52 weeks measure. I want to calculate value sales for the previous last 52 weeks (value sales from todays date to the same date last year).  I also want to calculate the same measure for one year ago.

I have tried this formula, but I dont know if this is the right one. Do you have any recommendations on this one, @Amit ?

Rolling 52W Value Sales = CALCULATE(
[Value Sales],
DATESBETWEEN('Calendar'[Date],
SAMEPERIODLASTYEAR(
NEXTDAY(
LASTDATE('Calendar'[Date]))),
LASTDATE('Calendar'[Date])
)
)
1 ACCEPTED SOLUTION
Community Support

Hi @PowerMarra ，

If only  rolling 52 weeks measure,try the following steps:

base data:

Step1, create new column:

``````weekStart =
var dayOfWeek = WEEKDAY('Table'[Date])
return
'Table'[Date] - dayOfWeek +1``````

Step2,use the below dax to create weekendnum:

``````runningMonthIndex =
var MinWeekStart = min('Table'[weekStart])
var weekNumber = roundup((DATEDIFF(MinWeekStart , [Date] , DAY)+1) / 7,0)
return
weekNumber``````

Step 3，use the below measure to get the sales 52 weeks ago:

``````test =
CALCULATE (
SUM ( 'Table'[sale] ),
FILTER (
ALL ( 'Table' ),
'Table'[runningMonthIndex]
>= MAX ( 'Table'[runningMonthIndex] ) - 52
&& 'Table'[runningMonthIndex] <= MAX ( 'Table'[runningMonthIndex] )
)
)``````

Final get:

And if you want to roll by years,try the measurte:

``````test1 =
CALCULATE (
SUM ( 'Table'[sale] ),
DATESBETWEEN('Table'[Date],
DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[Date])),DAY(MAX('Table'[Date])))  ,MAX('Table'[Date]))

)
``````

I tested your dax, but the data did not change in the new year：

Wish it is helpful for you!

Best Regards

Lucien

6 REPLIES 6
Community Support

Hi @PowerMarra ,

You could create a slicer to input date then get the sumvalue last 52weeks，Select the date you want to roll back 52 weeks.

Best Regards

Lucien

Community Support

Hi @PowerMarra ，

If only  rolling 52 weeks measure,try the following steps:

base data:

Step1, create new column:

``````weekStart =
var dayOfWeek = WEEKDAY('Table'[Date])
return
'Table'[Date] - dayOfWeek +1``````

Step2,use the below dax to create weekendnum:

``````runningMonthIndex =
var MinWeekStart = min('Table'[weekStart])
var weekNumber = roundup((DATEDIFF(MinWeekStart , [Date] , DAY)+1) / 7,0)
return
weekNumber``````

Step 3，use the below measure to get the sales 52 weeks ago:

``````test =
CALCULATE (
SUM ( 'Table'[sale] ),
FILTER (
ALL ( 'Table' ),
'Table'[runningMonthIndex]
>= MAX ( 'Table'[runningMonthIndex] ) - 52
&& 'Table'[runningMonthIndex] <= MAX ( 'Table'[runningMonthIndex] )
)
)``````

Final get:

And if you want to roll by years,try the measurte:

``````test1 =
CALCULATE (
SUM ( 'Table'[sale] ),
DATESBETWEEN('Table'[Date],
DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[Date])),DAY(MAX('Table'[Date])))  ,MAX('Table'[Date]))

)
``````

I tested your dax, but the data did not change in the new year：

Wish it is helpful for you!

Best Regards

Lucien

Regular Visitor

Thank you! How would you calculate rolling 52 weeks measure for one year ago? I need to see value sales for the last 52 weeks, and last 52 weeks one year ago.

Best regards, Marianne

Super User

@PowerMarra , new column in date or calendar

new columns
Week Start date = "Date"[Date]+-1*WEEKDAY("Date"[Date],2)+1 //monday week
Week End date = "Date"[Date]+ 7-1*WEEKDAY("Date"[Date],2)
Week Rank = RANKX(all("Date"),"Date"[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all("Date"),"Date"[Year Week],,ASC,Dense) //YYYYWW format

Last 52 weeks = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]>=max("Date"[Week Rank])-52 && "Date"[Week Rank]<=max("Date"[Week Rank])))

New Member

Fairly new PBI user. I am also trying to calculate a rolling 52 weeks and then a preceeding 52 weeks to calculate a % change. What formula do you suggest for the preceeding 52 weeks?

Frequent Visitor

Hi @amitchandak, the formula you mentioned Last 52 weeks = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week Rank]>=max("Date"[Week Rank])-52 && "Date"[Week Rank]<=max("Date"[Week Rank]))) only calculates the current 52 weeks and not the preceding 52 weeks, Is there a possibility there is some mistake in the formula? Sorry my PBI skills are still limited

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors