Forum Discussion
Solved
Condition for month/year not working
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"))))))))))))))))))))
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" )
3 Replies
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" )Thanks J.Payeras - this really helped solving the problem.
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 )
