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 ,
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
Super UserAnonymous , 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)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)+1ReturnIF(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
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