Add a leading 0 (zero) in a DAX FORMAT([Date], "yy") formula

[Jack2of3]

HI all, I've created a CALENDAR and can get everything functioning normally except when incrementing the "YY" by +1 in the following formula.

"FisPerName", UPPER(FORMAT([Date],"MMM")) & "-" &

SWITCH(TRUE(),Month([Date])<7,FORMAT([Date], "yy"),FORMAT([Date], "yy"))+1.

I expect it to be:

AUG-07 but get AUG-7....

CONCATENATING a 0 before the format doesn't work when the year is 10 or higher. Gives 010, 011, etc.

This is all the code:

DateTable = ADDCOLUMNS (

CALENDAR (DATE(2000,07,01), DATE(2035,06,30)),"Day of Month",DAY([Date]),

"Day",FORMAT([Date],"DDDD"),

"Year", FORMAT([Date],"YYYY"),

"Month", FORMAT([Date],"MMMM"),

    "Period", Month([Date]),

    "PerName", UPPER(FORMAT([Date],"MMM")) & "-" & FORMAT([Date], "yy"),

"Quarter", SWITCH( MONTH([Date]),

1, "Qtr1",

2, "Qtr1",

3, "Qtr1",

4, "Qtr2",

5, "Qtr2",

6, "Qtr2",

7, "Qtr3",

8, "Qtr3",

9, "Qtr3",

10, "Qtr4",

11, "Qtr4",

"Qtr4" ),

"FisYear", SWITCH(TRUE(),Month([Date])<7,YEAR([Date]),YEAR([Date])+1),

"FisQtr", SWITCH( MONTH([Date]),

1, "Qtr3",

2, "Qtr3",

3, "Qtr3",

4, "Qtr4",

5, "Qtr4",

6, "Qtr4",

7, "Qtr1",

8, "Qtr1",

9, "Qtr1",

10, "Qtr2",

11, "Qtr2",

"Qtr2" ),

"FisPer", SWITCH(TRUE(),Month([Date])<7,Month([Date])+6,Month([Date])-6),

    "FisPerName", UPPER(FORMAT([Date],"MMM")) & "-" & SWITCH(TRUE(),Month([Date])<7,FORMAT([Date], "yy"),FORMAT([Date], "yy"))+1)

[v-frfei-msft · Accepted solution]

Hi @Jack2of3 ,

To update your formula as below.

DateTable =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2000, 07, 01 ), DATE ( 2035, 06, 30 ) ),
    "Day of Month", DAY ( [Date] ),
    "Day", FORMAT ( [Date], "DDDD" ),
    "Year", FORMAT ( [Date], "YYYY" ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "Period", MONTH ( [Date] ),
    "PerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-"
        & FORMAT ( [Date], "yy" ),
    "Quarter", SWITCH (
        MONTH ( [Date] ),
        1, "Qtr1",
        2, "Qtr1",
        3, "Qtr1",
        4, "Qtr2",
        5, "Qtr2",
        6, "Qtr2",
        7, "Qtr3",
        8, "Qtr3",
        9, "Qtr3",
        10, "Qtr4",
        11, "Qtr4",
        "Qtr4"
    ),
    "FisYear", SWITCH ( TRUE (), MONTH ( [Date] ) < 7, YEAR ( [Date] ), YEAR ( [Date] ) + 1 ),
    "FisQtr", SWITCH (
        MONTH ( [Date] ),
        1, "Qtr3",
        2, "Qtr3",
        3, "Qtr3",
        4, "Qtr4",
        5, "Qtr4",
        6, "Qtr4",
        7, "Qtr1",
        8, "Qtr1",
        9, "Qtr1",
        10, "Qtr2",
        11, "Qtr2",
        "Qtr2"
    ),
    "FisPer", SWITCH (
        TRUE (),
        MONTH ( [Date] ) < 7, MONTH ( [Date] ) + 6,
        MONTH ( [Date] ) - 6
    ),
    "FisPerName", UPPER ( FORMAT ( [Date], "MMM" ) ) & "-"
        & IF (
            SWITCH (
                TRUE (),
                MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ),
                FORMAT ( [Date], "yy" )
            ) + 1 >= 10,
            SWITCH (
                TRUE (),
                MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ),
                FORMAT ( [Date], "yy" )
            ) + 1,
            0
                & SWITCH (
                    TRUE (),
                    MONTH ( [Date] ) < 7, FORMAT ( [Date], "yy" ),
                    FORMAT ( [Date], "yy" )
                ) + 1
        )
)

[Jack2of3]

Thank you very much. I was banging my head trying to figure that out.