Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Himanshu_1306
Resolver I
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.

 

Himanshu_1306_1-1670242634455.png

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: 

Himanshu_1306_0-1670242301563.png

 

1 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]

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

Please try these column expressions

 

FY = Year(EOMONTH('Date'[Date], -1))
 
FQ = Quarter(EOMONTH('Date'[Date], -1))
 
Pat
Microsoft Employee

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.

 

Himanshu_1306_0-1670246879535.png

 

Refer below screenshot for reference:

 

Himanshu_1306_1-1670247369304.png

 

 

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


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

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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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