Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.