cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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.

1 ACCEPTED SOLUTION
Super User

@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)))

2 REPLIES 2
Super User

@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)))

Anonymous
Not applicable

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors