Condition for month/year not working

[angie9127]

Can anyone help with this formula? I created a new column in my calendar table to name the quarter as follows. The first block works well, but when it has to look for the month again, it stops working and leaves everything blank as if there were no more conditions to look for.

FiscalQtr =

    IF(MONTH(Calendario[Date])in{10, 11, 12},

        IF(YEAR(Calendario[Date])in{2021},"FY2021Q3",

        IF(YEAR(Calendario[Date])in{2022},"FY2122Q3",

        IF(YEAR(Calendario[Date])in{2023},"FY2223Q3",

        IF(YEAR(Calendario[Date])in{2024},"FY2324Q3",

    IF(MONTH(Calendario[Date])in{1, 2, 3},

        IF(YEAR(Calendario[Date])in{2021},"FY2021Q4",

        IF(YEAR(Calendario[Date])in{2022},"FY2122Q4",

        IF(YEAR(Calendario[Date])in{2023},"FY2223Q4",

        IF(YEAR(Calendario[Date])in{2024},"FY2324Q4",

    IF(MONTH(Calendario[Date])in{4, 5, 6},

        IF(YEAR(Calendario[Date])in{2021},"FY2021Q1",

        IF(YEAR(Calendario[Date])in{2022},"FY2122Q1",

        IF(YEAR(Calendario[Date])in{2023},"FY2223Q1",

        IF(YEAR(Calendario[Date])in{2024},"FY2324Q1",

    IF(MONTH(Calendario[Date])in{7, 8, 9},

        IF(YEAR(Calendario[Date])in{2021},"FY2021Q2",

        IF(YEAR(Calendario[Date])in{2022},"FY2122Q2",

        IF(YEAR(Calendario[Date])in{2023},"FY2223Q2",

        IF(YEAR(Calendario[Date])in{2024},"FY2324Q2"))))))))))))))))))))

[Payeras_BI]

Hi again @angie9127 ,
Or this:

Your column ii =
"FY" & RIGHT ( YEAR ( 'Calendario'[Date] ) - 1, 2 ) & RIGHT ( YEAR ( 'Calendario'[Date] ), 2 ) 
& "Q" & IF ( QUARTER ( 'Calendario'[Date] ) = 1, 4, QUARTER ( 'Calendario'[Date] ) - 1 )

[Payeras_BI · Accepted solution]

Hi @angie9127 ,

Try this:

Your column = 
SWITCH(
    TRUE(),
    MONTH('Date'[Date]) in {10,11,12},"FY"&RIGHT(YEAR('Date'[Date])-1,2)&RIGHT(YEAR('Date'[Date]),2)&"Q3",
    MONTH('Date'[Date]) in {1,2,3},"FY"&RIGHT(YEAR('Date'[Date])-1,2)&RIGHT(YEAR('Date'[Date]),2)&"Q4",
    MONTH('Date'[Date]) in {4,5,6},"FY"&RIGHT(YEAR('Date'[Date])-1,2)&RIGHT(YEAR('Date'[Date]),2)&"Q1",
    MONTH('Date'[Date]) in {7,8,9},"FY"&RIGHT(YEAR('Date'[Date])-1,2)&RIGHT(YEAR('Date'[Date]),2)&"Q2"
)

[angie9127]

Thanks J.Payeras - this really helped solving the problem.