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!

Vijay_A_Verma

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

SolutionFollowing 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