How to get Week START and Week END Date? Week Start = Saturday & Week End = Friday) in my date table

[whatisdata96]

Hi all - question says it all.

I have a date table in my model. I need 2 columns. Week start = Saturday .. Week End Date = Friday.. If anyone could help, please provide DAX for this considering I have a date table with a date column in it.

[v-zhangti · Accepted solution]

Hi, @whatisdata96 

You can try the following methods.

Column:

Weekday = IF(WEEKDAY('Date'[Date])<=6,WEEKDAY('Date'[Date])+1,WEEKDAY('Date'[Date])-6)

Weeknum = CALCULATE(COUNT('Date'[Weekday]),FILTER('Date',[Weekday]=1&&[Date]<=EARLIER('Date'[Date])))

Week Start = CALCULATE(MIN('Date'[Date]),FILTER('Date',[Weekday]=1&&[Weeknum]=EARLIER('Date'[Weeknum])))

Week End = CALCULATE(MAX('Date'[Date]),FILTER('Date',[Weekday]=7&&[Weeknum]=EARLIER('Date'[Weeknum])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SecretChimpanze]

Week end date =date_column+(7-WEEKDAY(date_column,16))

Week start date =date_column-(7-WEEKDAY(date_column,16))

[amitchandak]

@whatisdata96 , I have two blogs for that, refer

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...

Refer the file attached after signature

In Power Qyery you can use Date.StartOfWeek with a number change start of week

Date.StartOfWeek and Date.EndOfWeek: https://www.youtube.com/watch?v=8HNicSedIds&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=104