How to get the ISO year in DAX while ISO week looks so easy this seems to be hard

[Spiedo]

I created a calendar using DAX.

For instance for value 3-1-2021 i get week 53 using underneath DAX formula

"YearWeeknumber", FORMAT ([Date], "YYYY" ) & "/" & FORMAT(WEEKNUM ([Date],21), "00"),

But the year thrown back is 2021 while it should be year 2020/53.

Hopefully it is very simple, could you give a hint? it's appreciated.

[Greg_Z]

I am in a similar situation, but I need to calculate the first day of the ISO year with the week starting on Sunday.  For example (in Month/Day/Year):

The first day of the ISO year 2016 = 12/27/2015
The first day of the ISO year 2017 = 12/25/2016
The first day of the ISO year 2018 = 12/31/2017
etc.

I have calculated the ISO year, and the ISO weeks (i.e. if WEEKNUM = 53, then count it as the first week of the following year), but any of the MIN or MINX functions that I have tried to calculate the first day of the ISO year are only brining back the first day of the year for the first ISO year (i.e. 12/27/2015 is being returned for all years as the first ISO date of the year).

[selimovd · Accepted solution]

Hey @Spiedo ,

you could check when the month = 1 and the WEEKNUM > 50 then you should use year - 1, otherwise year.

Do you want to use it as a calculated column or as a measure?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[sandee]

Hi Everyone, 

i am preparing a new calander table in my power bi model,

Please could you advise me as how to calculate "ISO Date" in any calender in power BI. i really need this column, please help me.

[Spiedo]

Thank you. I was trying to implement it but the ADDCOLUMNS( ) does not seem to appreciate the IF statement. It seems like a column under the hood of ADDCOLUMNS() can't be an IF statement because i retyped it into a very simple IF statement first. Nevertheless i believe this workaround will help

[selimovd]

Hey @Spiedo ,

it should also be possible with ADDCOLUMN. Can you share your measure?

Best regards

Denis

[Spiedo]

This is the beginning of the addcolumns, it specifically is about this column

"YearWeeknumber", FORMAT ([Date], "YYYY" ) & "/" & FORMAT(WEEKNUM ([Date],21), "00"),

If i turn an IF on it, I check the dates without FORMAT solely using the functions in the IF part, it is not accepted. Even if I use a very simple IF formule like IF(1>0;1;0) it is not accepted. I use PBI desktop october 2020 because we use this powerbi report server

Kalender = ADDCOLUMNS

        (

        CALENDAR (MIN(vCheckin_SupportCallList[Opened]),

        Now()),

        "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

        "Year", YEAR ( [Date] ),

        "Monthnumber", FORMAT ( [Date], "MM" ),

        "Weeknumber", FORMAT(WEEKNUM ([Date],21),00),

        "YearWeeknumber", FORMAT ([Date], "YYYY" ) & "/" & FORMAT(WEEKNUM ([Date],21), "00"),

        "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

[selimovd]

Hey @Spiedo ,

try the following approach:

Kalender = 
ADDCOLUMNS(
        CALENDAR (MIN(vCheckin_SupportCallList[Opened]), Now()),
        "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
        "Year", YEAR ( [Date] ),
        "Monthnumber", FORMAT ( [Date], "MM" ),
        "Weeknumber", FORMAT(WEEKNUM ([Date],21),00),
        "YearWeeknumber", FORMAT ([Date], "YYYY" ) & "/" & FORMAT(WEEKNUM ([Date],21), "00"),
        "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
        "YearWeeknumber_New",
            SWITCH(TRUE(),
                MONTH([Date]) = 1 && WEEKNUM ([Date],21) > 50, YEAR([Date]) -1,
                MONTH([Date]) = 12 && WEEKNUM ([Date],21) = 1, YEAR([Date])+1,
                YEAR([Date])
            ) & "/" & FORMAT(WEEKNUM ([Date],21), "00")
)

This will give you the new year if week is week 1 in the old year:

And also it will show you the old year when the week is the number of the old year:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Syndicate_Admin]

Good morning

what do you mean by "vCheckin_SupportCallList[Opened]" ??

Best regards

[Spiedo]

That is the name of a database view containing many fields, also date fields

[Spiedo]

Thank you very much!

BTW:

This also solves the first week of the year if it is in the previous year, thank for this extra tweak!

Quite special there is no ISO year while an ISO week is available