Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ISO Month in DAX

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#M3717

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg, 

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

       

      No ISO Month

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.