powerbi: handling iso weeks
There are some twist to keep in mind, when working with ISO calendar weeks. The first week of the year is defi
formulars#
formular for ISO calendar week
WEEKNUM(data["timestamp"], 21)
Hint: The parameter 21 is not always documented for PowerBi depending on the source.
formular for ISO year
For grouping we need to know to which year a calendar week belongs. It could be that the last week lays in the new year but for aggregation purpose these week counts to the previous year.
YEAR(data["timestamp"]-WEEKDAY(data["timestamp"],3)+3)
formular for monday
Calculate the date of monday from iso week and year.
DATE(data["iso_year"],1,-2)-WEEKDAY(DATE(data["iso_year"],1,3))+data["iso_cw"]*7
Detailed explanation Stackoverflow Answer