STARTOFWEEK and ENDOFWEEK functions in DAX


Use Case – Power Query language M offer two very useful functions – Date.StartOfWeek and Date.EndOfWeek. But DAX doesn’t offer any such function (even Excel doesn’t offer). 

The syntax of these are (in Power Query M)

 

 

Date.StartOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as any 

Date.EndOfWeek(dateTime as any, optional firstDayOfWeek as nullable number) as any  

 

 

Hence, you can define what should be your first day of week and then you get the Start of the Week and End of the Week. Below are sample outputs for these

STARTOFWEEK

SOW.png

ENDOFWEEK

EOW.png

Solution – Following DAX formulas can be used for STARTOFWEEK where Mon, Tue……Sun are firstDayOfWeek.

(Note – There are many alternative formulas for this purpose. I find the below ones the best)

 

 

Start of Week Monday = [Date]+1-WEEKDAY([Date]-1)

 

 

For Tuesday….Sunday, -1 argument will keep increasing. 

 

 

Start of Week Tuesday   = [Date]+1-WEEKDAY([Date]-2)
Start of Week Wednesday = [Date]+1-WEEKDAY([Date]-3)
Start of Week Thursday  = [Date]+1-WEEKDAY([Date]-4)
Start of Week Friday    = [Date]+1-WEEKDAY([Date]-5)
Start of Week Saturday  = [Date]+1-WEEKDAY([Date]-6)
Start of Week Sunday    = [Date]+1-WEEKDAY([Date]-7)

 

 

 Following DAX formulas can be used for ENDOFWEEK where Mon, Tue……Sun are firstDayOfWeek. 

In below formula, if I am saying End of Week Sunday, it means Sunday is firstDayOfWeek, hence answer for End of Week would be a Saturday as Saturday will be last day of week. 

 

 

End of Week Sunday = [Date]+7-WEEKDAY([Date]-7)

 

 

 For Saturday to Monday, -7 argument will keep decreasing

 

 

End of Week Saturday  = [Date]+7-WEEKDAY([Date]-6)
End of Week Friday    = [Date]+7-WEEKDAY([Date]-5)
End of Week Thursday  = [Date]+7-WEEKDAY([Date]-4)
End of Week Wednesday = [Date]+7-WEEKDAY([Date]-3)
End of Week Tuesday   = [Date]+7-WEEKDAY([Date]-2)
End of Week Monday    = [Date]+7-WEEKDAY([Date]-1)

 

 

Below is the sample pbix file which has these formulas and can be used for testing

https://1drv.ms/u/s!Akd5y6ruJhvhuXZi2OY4Lo41w5rL?e=h1CT4M 



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*