Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Rolling 4 weeks calculation

Hi, I am calculating Rolling 4 weeks data based on below  W1=W1 W2=W1+W2 W3=W1+W2+W3 W4=W1+W2+W3+W4 W5=W2+W3+W4+W5(Starting from W2) ..... W1 is 01-Jan-2020 to 07-Jan-2020 W2 is 08-Jan-2020 ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try like

Week Start With Year = var _1=  QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)+1 return if(_1<52,_1,52)

Week Start Date = var _1 =QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)*7 return STARTOFYEAR('Date'[Date]) + if(_1<52,_1,52)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak , I tried like below and i am getting week 53 days in week52.

Week_number =
Var WN = INT((INT('Calendar'[Date] - DATE(YEAR([Date]),1,1)))/7)+1
Return
IF(WN > 52,52,WN)


Can you please help me now for rolling 4 weeks data Current years vs Lastyear in line chart.
Each week should have 4 weeks data(current week+previous 3 weeks).


Best Regards,
Prasad

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Thanks for pointing out

    This was wrong

    Week Start Date = var _1 =QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7) return STARTOFYEAR('Date'[Date]) + if(_1<52,_1,51)*7