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 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
13 Replies
Anonymous , refer my blog for week
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 :
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
PrasadAnonymous , 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
