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 ,

This should give correct week, that starts every year. And Week start date.

Week No Start With Year = QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)+1
Week Start Date = STARTOFYEAR('Date'[Date]) + QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)*7

 

I have given formula above how to get rolling 4 week for values.

Or you can follow this

Check https://www.youtube.com/watch?v=duMSovyosXE

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak ,
Is it possible to Remaining days in the year to be assigned to week 52.

Means we will have 7 days in a week (7 * 52 = 364) Remainig one day instead of week53 can we have it in Week52(8 days) .
For non leap year week52 will have 8 days.
leap year week52 will have 9 days.


  • 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 , This logic is giving below results,

      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