Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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.
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.
Week end date =date_column+(7-WEEKDAY(date_column,16))
Week start date =date_column-(7-WEEKDAY(date_column,16))
@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
User | Count |
---|---|
17 | |
14 | |
13 | |
13 | |
13 |
User | Count |
---|---|
19 | |
15 | |
14 | |
10 | |
9 |