Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
Does anyone know the DAX for displaying the Week Commencing date?
I am working on a service desk based on tickets and we have a column for the date received of the ticket.
Now i would like to display the date of the week commencing in a new column, based on that date received column.
Thanks.
Solved! Go to Solution.
Hey,
just try this "simple" DAX statement
SoWDate = 'Calendar'[Date] - WEEKDAY('Calendar'[Date],2) +1
The second parameter of the WEEKDAY()-function indicates if Sunday or Monday is your first day of the week, for me this works like a charm, maybe you have to use a different correction part.
Just using your Date column and the "Day of Week" column helps to adjust the above mentioned formula if necessary.
and this calculates the End Date of the week
EoWDate = 'Calendar'[Date] + 7 - WEEKDAY([DATE],2)
Hope this helps
Regards
Week commencing cannot be sorted in chronological order
Please help.
Hey @Anonymous ,
please consider to start a new thread, don't forget being more specific about your issue.
If possible provide a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link. If you useExcel to create the sample data, upload the xlsx as well.
Regards,
Tom
Hey,
just try this "simple" DAX statement
SoWDate = 'Calendar'[Date] - WEEKDAY('Calendar'[Date],2) +1
The second parameter of the WEEKDAY()-function indicates if Sunday or Monday is your first day of the week, for me this works like a charm, maybe you have to use a different correction part.
Just using your Date column and the "Day of Week" column helps to adjust the above mentioned formula if necessary.
and this calculates the End Date of the week
EoWDate = 'Calendar'[Date] + 7 - WEEKDAY([DATE],2)
Hope this helps
Regards
Well, only if you accept that week 1 can start in the year before this, in quite a lot of countries week 1 is the week 1ith 1 January in it and can be 1-7 days. That complicates matters
Thanks. It helps.
This is great. Could someone please explain the logic behind this.
Hey,
thanks for your kind words!
The thinking (not sure if Mr Spock would call this logic) behind this is as follows:
Hopefully this explains the reasoning behind both DAX statements a little better.
Regards
Tom
Brilliant! Spock would be proud. Straight forward logic and you can change the number for subtraction and addition by multiples of 7 to get the starting/ending dates for any number of weeks prior or future. Thanks!
Hi, I don't have much time to explain but I can point you in the direction of this article:
https://powerpivotpro.com/2014/04/week-ending-date-calculation/
Hope that helps.
Dates are not stored that way in DAX.
This may work in Excel but I don't think it's ok in PowerBI.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
80 | |
64 | |
52 | |
48 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |