I have a Table which my weekly report fetches data from. The Table is updated every week, so the data are in form of Week 1, Week 2,..., Week 5. I have other date information such as Year, and Month, but no specific days.
With this available information, I hope there is a DAX function (FUNC) that will take Year, Month, and Week number and return the corresponding weekend date.
Say, For March 2024 Week 2, I can have something like:
FUNC (2024,03,02) = DATE (2024,03,09)
Below is what I have tried.
Since there's no date column from the data, so I introduced a surrogate date that takes Year, Month and takes 1 (Constant) for day.
So, if week 3 February 2024 data is to be added to the Table,
the derived date will be DATE (2024,02,01) .
The limitation with this is that I cannot accurately carry out month-on-month, quarter-on-quarter, and year-on-year calculations, as the date is always pointing to first day of the month.
