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
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
Be the first to comment