ISO Month in DAX

[Anonymous]

Anyone knows how to write a DAX formula to obtain an ISO month (4-4-5 calendar)? I´ve looked everywhere but no luck.

Here is ISO week and year: https://community.powerbi.com/t5/Desktop/Need-help-creating-week-number/m-p/14220/highlight/true#M37...

Here, Marco Russo uses ISO quarters and months but can´t find how he gets them: https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ 

Best regards.

[v-alq-msft]

Hi, @Anonymous 

Have you tried the measure suggested in the first link? You may try like the following dax codes to create a calculated table.

Calendar = 
var tab = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR([Date]),
    "MonthNum",MONTH([Date]),
    "Iso Week",WEEKNUM([Date],21)
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Iso Year",
    IF(
        AND(
            WEEKNUM([Date],21)<5,
            WEEKNUM([Date],2)>50
        ),
        [Year]+1,
        IF(
            AND(
                WEEKNUM([Date],21)>50,
                WEEKNUM([Date],2)<5
            ),
            [Year]-1,
            [Year]
        )
    ),
    "Iso Year Week",
    IF(
        AND(
            WEEKNUM([Date],21)<5,
            WEEKNUM([Date],2)>50
        ),
        [Year]+1,
        IF(
            AND(
                WEEKNUM([Date],21)>50,
                WEEKNUM([Date],2)<5
            ),
            [Year]-1,
            [Year]
        )
    )&"week"&
    FORMAT(WEEKNUM([Date],21),"00")
)

return
newtab

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft There is no ISO month in that formula, just regular natural month.

[Anonymous · Accepted solution]

This question was a bit silly, I just realized that once you have the ISO week

Semana = WEEKNUM(Calendario[Date]; 21)

 You just also have the month (for a reason it´s called a 4-4-5 calendar...) lol

Iso Mes = 
SWITCH(
    TRUE();
    Calendario[Semana] <= 4; "Enero";           // 4
    Calendario[Semana] <= 8; "Febrero";         // 4
    Calendario[Semana] <= 13; "Marzo";          // 5

    Calendario[Semana] <= 17; "Abril";          // 4
    Calendario[Semana] <= 21; "Mayo";           // 4
    Calendario[Semana] <= 26; "Junio";          // 5

    Calendario[Semana] <= 30; "Julio";          // 4
    Calendario[Semana] <= 34; "Agosto";         // 4
    Calendario[Semana] <= 39; "Septiembre";     // 5

    Calendario[Semana] <= 43; "Octubre";        // 4
    Calendario[Semana] <= 47; "Noviembre";      // 4
    "Diciembre"                                 // 5
)

Best regards.

[Greg_Deckler]

Perhaps it is from here:

"Colin Banfield wrote a useful Excel workbook to generate Calendar tables. Darren Gosbell offers an interesting Power Query script to generate a Calendar table."

[Anonymous]

Hi Greg, 

I downloaded the workbook, sadly there is no iso month (I hope I´m not missing anything)... : (

No ISO Month