Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |