DAX – Getting the Start Date of the following Week
I was recently doing some work for a customer where they wanted to compare their date to the start date of the following week. They also had a requirement to persist the data to the dataset.
I thought that this would be a good challenge to use DAX with a calculated column. I most certainly could do this using the Power Query Editor and M, but I always like to see if it is possible when it is a smaller and easier DAX calculation.
Below is the DAX to get the Start date for the following week
'Date'[Date] - WEEKDAY('Date'[Date],2)+8
As you can see below, as each week goes on, I have got the start date for the following week for each day of the current week.
If I wanted to change it and use the starting date of the current week and use it for all dates for the current week, I could change it with the following DAX calculation
'Date'[Date] - WEEKDAY('Date'[Date],2)+1
As always if there are any comments or suggestions please leave them in the comments section below.
Similarly, we need to base calculations after a certain day. Would there be a similar method to calculate the first Monday of each month?
Hi there,
Would you want the first Monday of each month to be replicated across all the dates for that month?
Hi Gilbert,
if your week starts on a Monday you can use ‘3’ in the second parameter of WEEKDAY() instead of ‘2’.
startdatecurrentweek = ‘Date'[Date] – WEEKDAY(‘Date'[Date],3)
startdatefollowingweek = ‘Date'[Date] – WEEKDAY(‘Date'[Date],3) + 7
It’s more or less the same as your expression but the ‘startdatecurrentweek’ doesn’t have to have a number added to adjust the calculation and the ‘startdatefollowingweek’ adds the number of days in a week. (A Gregorian week anyway)
Best regards Jes.
Very Useful! Thanks!
Awesome, thanks for letting me know.