I am generating a Calendar table which can then be used to create a Gantt Chart visual on Power BI.
I have generated a list of dates in Power Query. Now I need the week number and year values for each of these dates.
The M query has a function
Date.WeekOfYear
, but this does not return the ISO Week number.
each Date.WeekOfYear([DateColumn], Day.Monday)
On the other hand, DAX has a function
WEEKNUM
which gives the ISO Week number.
ISOWeekNumber = WEEKNUM('Calendar'[DateColumn], 21)
I understood from looking up on the internet that DAX is more measures and calculated columns.
So, in this case, should I use Power Query or DAX if I want Year, Week Number, Week of the Day Number, etc.? What is the right thing to do and what are the implications of choosing one over another?
The algorithm to generate ISO week number looks complicated in Power Query and there seems to be no ready function to generate ISO week number.