Calculating rolling 52 weeks

[PowerMarra]

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])

)

)

[v-luwang-msft]

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

[v-luwang-msft · Accepted solution]

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:

About this solutions,you could refer: https://community.powerbi.com/t5/Desktop/Rolling-52-week-sales-and-preceding-52-week-sales/m-p/72014... 

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

[PowerMarra]

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

[amitchandak]

@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])))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[bp77]

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?

[foyadeyi]

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