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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started



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





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!Akd5y6ruJhvhuXZi2OY4Lo41w5rL?e=h1CT4M