How to get Previous Custom Week end date

[Anonymous]

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.

[amitchandak · Accepted solution]

@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

[Anonymous]

Thank you so much @amitchandak that was brilliant! It worked perfectly.