ISO 8601 Date table has 2 W52D6 in 2022

[Schwinn123]

Hi guys

I have created a date table with dax.  My company uses ISO 8601 calandar. So to get the week number in DAX I used WEEKNUM( [date] ,21). But we can see 2022/01/01 is considered as W52D6. While 2022/12/31 is also considered as W52D6.  How do we avoid this kind of issue? Below is the DAX of the the Date table.

DateDim = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 4, 1 ), DATE ( ( YEAR ( TODAY () ) + 1 ), 1, 1 ) ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "mmm YYYY" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter",
        FORMAT ( [Date], "YYYY" ) & "/Q"
            & FORMAT ( [Date], "Q" ),
    "Weeknumber", WEEKNUM ( [Date],21 ),
    "Dateindicator",
        MID ( YEAR ( [Date] ), 3, 2 ) & "W"
            & WEEKNUM ( [Date],21 ) & "D"
            & WEEKDAY ( [Date], 2 ),
    "Week",
            MID ( YEAR ( [Date] ), 3, 2 ) & "W"
            & WEEKNUM ( [Date],21 ))

[v-jingzhang]

Hi @Schwinn123 

Thanks to the DAX code for ISO Year provided by @serpiva64 , I include it into your Date table code as below. Hope it is helpful for other users in need. 

DateDim = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 4, 1 ), DATE ( ( YEAR ( TODAY () ) + 1 ), 1, 1 ) ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "mmm YYYY" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter",
        FORMAT ( [Date], "YYYY" ) & "/Q"
            & FORMAT ( [Date], "Q" ),
    "Weeknumber", WEEKNUM ( [Date],21 ),
    "IsoYear", YEAR([Date]+26-WEEKNUM ( [Date],21 )),
    "Dateindicator",
        MID ( YEAR([Date]+26-WEEKNUM ( [Date],21 )), 3, 2 ) & "W"
            & WEEKNUM ( [Date],21 ) & "D"
            & WEEKDAY ( [Date], 2 ),
    "Week",
            MID ( YEAR([Date]+26-WEEKNUM ( [Date],21 )), 3, 2 ) & "W"
            & WEEKNUM ( [Date],21 ))

Best Regards,
Community Support Team _ Jing

[Schwinn123]

Hi  Thank both of you @serpiva64 @v-jingzhang for your efforts on this topic.  It works for me.  But I can only choose one reply as Accept as Solution.  

[serpiva64 · Accepted solution]

Hi,

i think the problem is not on the day but on the year because 01/01/2022 should be 21W52D6 and not 22W52D6.

You need to add IsoYear (from a post of Jānis Stūris and i compared the result with another power query version of IsoWeekYear for the next 2000 years and returns the correct IsoYear)

"IsoYear", (year(Query1[Date]+26-Query1[ISOWeekNum])))

and use it in your DateIndicator

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !