Rolling 4 weeks calculation

[Anonymous]

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 to 14-Jan-2020.

Now my issues is i have data till Week20 in my current year.
But i am getting data for Week21[18+19+20] ,Week22[19+20], Week23[20] also.
Can you please help me to show only data till actual week.
I have created one table like below

Weeknum	Weeks
1	1
2	1
2	2
3	1
3	2
3	3
4	1
4	2
4	3
4	4
5	2
5	3
5	4
5	5
6	3
6	4

.............................................
Please find the below image

[Anonymous]

Hi,

Please share the calculation that you used for calcuting the rolling average

[dax]

Hi @Anonymous , 

I think this might be caused by weeknum, I find that you seem to re-define the week num, and today(5/20) should be in 21 week. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax ,
I am calculating weeks based on the customer requirment.
i.e: Week1 (01-Jan-2020 to 07-Jan-2020)
Thats why we are getting week numbers like this.

Best Regards,
Prasad

[dax]

Hi @Anonymous ,

Did you try my sample data? I re-define the calendar date ( each 7 days as  a week from start of year). You could check whether this is what you want.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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]

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

Best Regards,
Prasad

[amitchandak]

@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]

@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]

@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]

@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]

@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

[Anonymous]

@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]

@Anonymous , refer my blog for week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Columns in Date Table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

Measures

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
rolling 4 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 4 period Sales =
Var _min = maxx(allselected('Date','Date'[Week Rank]) -4
Var _max = maxx(allselected('Date','Date'[Week Rank]) 
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

My Webinar :

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184