topic Condition for month/year not working in DAX Commands and Tips

Condition for month/year not working

angie9127 — Mon, 30 Jan 2023 05:13:17 GMT

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"))))))))))))))))))))

Re: Condition for month/year not working

Payeras_BI — Mon, 30 Jan 2023 08:06:37 GMT

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" )

Re: Condition for month/year not working

Payeras_BI — Mon, 30 Jan 2023 10:04:42 GMT

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 )

Re: Condition for month/year not working

angie9127 — Mon, 30 Jan 2023 16:15:21 GMT

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