Fiscal Year and Fiscal Quarter

[Himanshu_1306]

Hi All,

How should I calculate Fiscal Year and Fiscal Quarter in the below scenario.

We follow Month-End approach so for Q1 we show Feb-April numbers, for Q2 = May-Jul , Q3 = Aug - Oct and for Q4 = Nov- Jan numbers.

My date table has Quarter numbers but I want to add Fiscal Year and Fiscal Quarter to the table like above for smooth processing.

Kindly advice how to approach this?

My Date Table: 

[ppm1]

Please try these column expressions

FY = Year(EOMONTH('Date'[Date], -1))

FQ = Quarter(EOMONTH('Date'[Date], -1))

Pat

[Himanshu_1306]

Something is wrong here.

Fiscal Year 2023 Q4 i.e Current Quarter should show month as Dec'2022 not Dec'2023. Current Year will show 2022 but fiscal year will be 2023.

Refer below screenshot for reference:

[Himanshu_1306]

I'm trying with the below calculation but not able to add Jan month in Q4.

Q1 -> Feb - April

Q2 -> May-Jul

Q3 -> Aug- Oct

Q4 -> Nov-Jan

Fiscal Year should be 2023 for these quarters.

FY = IF(AND([Year] =2020,M_Date_Table[Month Num]<=12),"FY21",

IF(AND([Year] =2020,M_Date_Table[Month Num]<=6),"FY21",

IF(AND([Year] =2021,M_Date_Table[Month Num]<=12),"FY22",        

IF(AND([Year] =2021,M_Date_Table[Month Num]<=6),"FY22",

IF(AND([Year] =2022,M_Date_Table[Month Num]<=12),"FY23",

IF(AND([Year] =2022,M_Date_Table[Month Num]<=6),"FY23",

IF(AND([Year] =2023,M_Date_Table[Month Num]<=12),"FY24",

IF(AND([Year] =2023,M_Date_Table[Month Num]<=6),"FY24",

IF(AND([Year] =2024,M_Date_Table[Month Num]<=12),"FY25",

IF(AND([Year] =2024,M_Date_Table[Month Num]<=6),"FY25",

" "))))))))))

[Himanshu_1306 · Accepted solution]

I've figured out the solution:

1st Step - Create FY Column

FY = IF(AND([Year] =2020,M_Date_Table[Month Num]=1),"2020",

IF(AND([Year] =2020,M_Date_Table[Month Num]<=12),"2021",

IF(AND([Year] =2021,M_Date_Table[Month Num]=1),"2021",        

IF(AND([Year] =2021,M_Date_Table[Month Num]<=12),"2022",

IF(AND([Year] =2022,M_Date_Table[Month Num]=1),"2022",

IF(AND([Year] =2022,M_Date_Table[Month Num]<=12),"2023",

IF(AND([Year] =2023,M_Date_Table[Month Num]=1),"2023",

IF(AND([Year] =2023,M_Date_Table[Month Num]<=12),"2024",

IF(AND([Year] =2024,M_Date_Table[Month Num]=1),"2024",

IF(AND([Year] =2024,M_Date_Table[Month Num]<=12),"2025",

" "))))))))))

2nd - Create Fiscal Quarter

FQ = Quarter(EOMONTH(M_Date_Table[Date], -1))

3rd - 

Fiscal Year Quarter = M_Date_Table[FY] & "Q" & M_Date_Table[FQ]