Forum Discussion

PowerMarra's avatar
PowerMarra
Regular Visitor
5 years ago
Solved

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])
)
)
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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/720148 

    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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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/720148 

    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's avatar
      PowerMarra
      Regular Visitor
      5 years ago

      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's avatar
    amitchandak
    Super User
    5 years ago

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • foyadeyi's avatar
      foyadeyi
      Frequent Visitor
      3 years ago

      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

    • bp77's avatar
      bp77
      New Member
      3 years ago

      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?

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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