cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors