Forum Discussion
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 ...
amitchandak , For all years weeks will have same days(7 days).
like week1 (1 jan to 7 jan)
Best Regards,
Prasad
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
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.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)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)