Forum Discussion

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 ...

Super User

6 years ago

Anonymous , these week are always Wed-Tue or only for this year ??

In any case if you have week start date you should able create the rank and other column to get desired data

Anonymous

Not applicable

6 years ago

amitchandak , For all years weeks will have same days(7 days).
like week1 (1 jan to 7 jan)

Best Regards,
Prasad

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