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.
for standard work week given this formula on another post https://community.powerbi.com/t5/Desktop/Need-to-calculate-the-week-ending-dates-for-the-complete-ye...
adapted, it serves for Mon-Sun workweek
EOWeekMoSu = [Date] - MOD([Date]-2,7)+6
but, I achieved only through trial error.
Can someone explain how this formula works?
Solved! Go to Solution.
Hey @v-shex-msft,
and if you use
[Date] + 7 - WEEKDAY([DATE],2)
you receive the End-Date of the week as requested.
Personally I like the Weekday approach due to its readibility, but I have to admit that the MOD() approach has some kind of geeky beauty and besides that MOD is one of most underrated functions 😉
Hi @hxkresl,
I'd like to suggest you use weeknum/weekday functions with type 2, you can use this to create a monday to sunday work week.
weekday = WEEKDAY([Date],2) weeknum = WEEKNUM([Date],2)
Regards,
Xiaoxin Sheng
Hey @v-shex-msft,
and if you use
[Date] + 7 - WEEKDAY([DATE],2)
you receive the End-Date of the week as requested.
Personally I like the Weekday approach due to its readibility, but I have to admit that the MOD() approach has some kind of geeky beauty and besides that MOD is one of most underrated functions 😉
is there a way to use this formula as a calculated colomn, but instead of returning the date/time at 12AM, the time is set for 1AM?
For example, if OrderedDateLocal is a weekend, filter hours weekend will return the date for the following Monday at 1AM. Right now it is defaulting to 12AM. My formula for filter hours weekend below:
filter hours weekend = IF(WEEKDAY(vReviewOrderSLA[OrderedDateLocal],2)=6 || WEEKDAY(vReviewOrderSLA[OrderedDateLocal],2)=7,
(vReviewOrderSLA[OrderedDateLocal] - MOD(vReviewOrderSLA[OrderedDateLocal]-2, 7) + 7), vReviewOrderSLA[OrderedDateLocal])
Hey,
I give it a try
starting with MOD():
MOD returns the remainder of a integer division, meaning MOD(7,4) returns 3 whereas MOD(9,4) returns 1
Testing
MOD("2016-01-01",7) returns 6 (a Friday)
MOD("2016-01-02",7) returns 0 (Saturday)
This in combination with MOD([DATE]) we learn 2 things, first
Now we have to do some mindboggling indexing stuff
Hope this explains a little
Is there a method to display weeks if no data exists for that week? I need to show a graph with totals by week - which this formula does beautifully - but in a four week period, if one of those weeks does not have data it does not generate a 0 bar as a place holder for that week. It simply omits that week.
I used this formula and it works great except that it seems to eliminate "0" data bars from the graph if there is no activity in that week.
EOWeekMoSu = [Date] - MOD([Date]-2,7)+6
Thanks!
Signed,
Stumped in MEM
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |