Turn the year from a datetable into ISO-Year

[Applicable88]

Hello,

I have a standard datetable like US-Standards. In europe ISO - standard is common. The first week of the year needs to have at least 4 days in January. So according to ISO the first week in 2021 starts on Jan 4th. So the first three days in Jan are 2020/53. I use the weeknum() function with return type 21 to generate the right weeknumbers. I use the function  CombinedYearWeek= Calendar[Year]&"/"& format(WEEKNUM(Calendar[Date],21),"00") to generate a column á la YYYY/WW, which results having the years of this year, but the calendarweek 53 belongs to 2020. How can I generate a column like  "ModifiedYear"in my sample table, that all dates get the right year according to ISO, so I can combine it with the weeknum()-function correctly. 

Date	Calendar Year	Week	Weeknum()		CombinedYearWeek	ModifiedYear
2021-01-01	2021	01	53		2021/53	2020
2021-01-02	2021	01	53		2021/53	2020
2021-01-03	2021	01	53		2021/53	2020
2021-01-04	2021	02	01		2021/01	2021

What I get instead looks like this, a slicer sort descending including todays week:

This would imply that week 53 belongs to this year, which is wrong, it belongs to last year hence 2020/53 should be right. Since I have no data after week38, which is the week today it also is misleading.

Hope someone has a solution.

Best. 

[amitchandak]

@Applicable88 , Try to take year from week start date

Monday

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

Sunday

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

week Year = year[Week Start Date])

[Applicable88]

Oh @amitchandak, I was wrong. That formula is not going to work, because its not adapting that every year changes. For this year it might work, but for example when January 2019 really had 4 days in the first week, the remaining two days of December 2018 , means the 30th and 31st belongs to the 2019 hence are calendarweek 2019/01 according to ISO. Any other methods?

[Applicable88 · Accepted solution]

I found a solution:

First we need the correct calendar weeks according to ISO. Then the year can be calculated from that "correct" week column, with another calculated column:

ISOYear = YEAR('Date'[Date] + 26 - 'Date'[ISOWeekNumber])