Forum Discussion
How to get Previous Custom Week end date
Hi everyone,
I'm fairly new to Power BI and DAX and haven't found a solution that fits my current problem. I'd like to calculate the totals for previous week. My date table contains the custom week end column. For example if date 4th November 2020, the custom weekend date is 8/11/2020. I'd like to find out if there's a way to compare to the total from previous week end date depending on the selected date on the filter. If the user selects 28 October 2020, I'd like to find out the total from previous week = 25/10/2020.
Any help or advise will be really appreciated.
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
2 Replies
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/1051123Thank you so much amitchandak that was brilliant! It worked perfectly.