cancel
Showing results for
Did you mean:
Resolver I

Fiscal Year and Fiscal Quarter

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:

1 ACCEPTED SOLUTION
Resolver I

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]
4 REPLIES 4
Solution Sage

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

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

Pat
Microsoft Employee
Resolver I

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:

Resolver I

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

" "))))))))))
Resolver I

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]

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors