Forum Discussion
Custom Work Week Calculation (DAX or M/PQ)
I have the [Date] and the [Day Name] in the below table. I need to calculate the [End of Week] Column. This is a Wedensday-Tuesday workweek. I am fine with doing this in DAX or Power Query. Suggestions are much appreciated.
| Date | Day Name | End of Week |
| 12/1/2019 | Sunday | 12/3/2019 |
| 12/2/2019 | Monday | 12/3/2019 |
| 12/3/2019 | Tuesday | 12/3/2019 |
| 12/4/2019 | Wednesday | 12/10/2019 |
| 12/5/2019 | Thursday | 12/10/2019 |
| 12/6/2019 | Friday | 12/10/2019 |
| 12/7/2019 | Saturday | 12/10/2019 |
| 12/8/2019 | Sunday | 12/10/2019 |
| 12/9/2019 | Monday | 12/10/2019 |
| 12/10/2019 | Tuesday | 12/10/2019 |
| 12/11/2019 | Wednesday | 12/17/2019 |
| 12/12/2019 | Thursday | 12/17/2019 |
| 12/13/2019 | Friday | 12/17/2019 |
| 12/14/2019 | Saturday | 12/17/2019 |
| 12/15/2019 | Sunday | 12/17/2019 |
| 12/16/2019 | Monday | 12/17/2019 |
| 12/17/2019 | Tuesday | 12/17/2019 |
| 12/18/2019 | Wednesday | 12/24/2019 |
| 12/19/2019 | Thursday | 12/24/2019 |
| 12/20/2019 | Friday | 12/24/2019 |
| 12/21/2019 | Saturday | 12/24/2019 |
| 12/22/2019 | Sunday | 12/24/2019 |
| 12/23/2019 | Monday | 12/24/2019 |
| 12/24/2019 | Tuesday | 12/24/2019 |
Thank you
End of Week = [Date]+MOD(3-WEEKDAY([Date]),7)
4 Replies
See if my Week Ending Quick Measure meets your needs: https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120
Greg_Deckler This uses the WEEKNUM function which only works for weeks with a Sunday and Monday start. Let me know if there is any easy way to change this for a Wednesday start. Also, not sure if a measure will work for my needs. I was hoping to have a column added to my date dimension table via DAX, M/PW.
Thanks
While undocumented, you can actually use WEEKNUM([Date],13) to get weeks starting on Wednesday.
https://exceljet.net/excel-functions/excel-weeknum-function
I realize that this is for Excel, but those all work with WEEKNUM in DAX as well. You can easily convert measures to columns, generally by just removing the aggregations in front of column references.
End of Week = [Date]+MOD(3-WEEKDAY([Date]),7)
