cancel
Showing results for
Did you mean:

## 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

ENDOFWEEK

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

Top Kudoed Posts
Latest Articles
Archives
Polls
What is your favorite Power BI feature release for June 2022?