Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
New Fiscal year starts in February 1st. So far,I didn't use year column and only we used week,I could do easily by taking max of week number and getting 5 weeks back from that. But right now we have another fiscal year coming so I think year column needs to be clubbed with week to get unique year week number
Right now, that column looks like the below
202248
202249
202250
202251
202252
202301
202302
Now I can't use max and subtract 5 to get last 5 weeks. How do I do this?
Solved! Go to Solution.
@Imagauthamam , If you want this based on today
Last 5 Week Today =
var _min1 = today() -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min1 +6
var _min = _min - 4*7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
If you need based on selected value, ROlling 5 weeks data
You need columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
Last 5 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
But if you need 5 week trend when select 1 week, you need one more date table (independent)
//Date1 is independent Date table and Date is Joined
new measure =
var _max = max('Date'[Week Rank])
var _min = max('Date'[Week Rank]) -5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
@Imagauthamam , If you want this based on today
Last 5 Week Today =
var _min1 = today() -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min1 +6
var _min = _min - 4*7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
If you need based on selected value, ROlling 5 weeks data
You need columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
Last 5 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
But if you need 5 week trend when select 1 week, you need one more date table (independent)
//Date1 is independent Date table and Date is Joined
new measure =
var _max = max('Date'[Week Rank])
var _min = max('Date'[Week Rank]) -5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |