cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hxkresl
Helper V
Helper V

End of Week DAX for non standard workweek: Mon - Sun

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?  

1 ACCEPTED 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 😉



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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)

5.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 😉



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

Capture.JPG

TomMartens
Super User
Super User

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

  • the date is implicitly converted to an integer
  • SATURDAYs mark a complete week

Now we have to do some mindboggling indexing stuff

  • We have to consider that Mondays have to become the first day of the week, starting with zero
  • -2 because Friday is lacking to days of a full week (Ending with Sunday)
    • Friday: MOD("2016-01-01"-2,7) returns 4
    • Sunday: MOD("2016-01-03",2-7) returns 6 this has to become zero due to the fact that finally we want to add (beware that a substraction of a negative number results in plus)  a certain number of days to get the end of the week and for this reason we have the second paramter

Hope this explains a little



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors