Forum Discussion
How to get Previous Custom Week end date
- 5 years ago
Anonymous , Assuming you have week start date.
Refer to my blog to have week start on any date of week if needed - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
Columns you need in your week/date table(You need to have a date/week Table)
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Assume you have , this for Monday week
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) //No need option
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) //add this on week start date or YYYYWWThese measures will work
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))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))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-Last-Week/ba-p/1051123
Anonymous , Assuming you have week start date.
Refer to my blog to have week start on any date of week if needed - https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
Columns you need in your week/date table(You need to have a date/week Table)
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Assume you have , this for Monday week
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) //No need option
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense) //add this on week start date or YYYYWW
These measures will work
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))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
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-Last-Week/ba-p/1051123
- Anonymous5 years agoNot applicable
Thank you so much amitchandak that was brilliant! It worked perfectly.