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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Create a custom Financial Year Column

Hi, 

 

I need to create a new column to record a financial year which runs from 1st September to 31st August. What I would like to have in as the out put would be, as an example, FY21/22.

 

Any idea how I can look to create this, please? I have data in my calendar table that runs from 1st Jan 2014 to 31st December 2022

 

I have found this which creates the Fiscal Year, but not too sure how I can amend this to show the year period that I need

fiscal year =

var curryear = right(year('date'[date]),2)
var lastyear = right(year('date'[date])-1,2)
var newyear = right(year('date'[date])+1,2)

var fiscalyear =

switch(
true(),
month('date'[date]) >=4,
curryear & "-" & newyear,
lastyear & "-" & curryear

)
return

fiscalyear
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Start date of the financial year = 
IF(MONTH([Date])<9,DATE(Year([Date])-1,9,1),DATE(Year([Date]),9,1))
financial year = YEAR([Start date of the financial year])
FY = 
var curryear = right([financial year],2)
var newyear = right([financial year]+1,2)
return
curryear&"-"&newyear

vzhangti_0-1657244639806.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

Start date of the financial year = 
IF(MONTH([Date])<9,DATE(Year([Date])-1,9,1),DATE(Year([Date]),9,1))
financial year = YEAR([Start date of the financial year])
FY = 
var curryear = right([financial year],2)
var newyear = right([financial year]+1,2)
return
curryear&"-"&newyear

vzhangti_0-1657244639806.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you for these formulas. I needed to calculate academic year, which is similar, but starts on July 1. This is the formula I came up with (with your help). Is it right?

 

Academic Year =
var spring = IF(MONTH([Grad Date])<7,Year([Grad Date]),YEAR([Grad Date])+1)
var fall = IF(MONTH([Grad Date])<7,YEAR([Grad Date])-1,YEAR([Grad Date]))
return fall&"-"&spring
 
It seems like the same thing, but I don't need to calculate the first day of the year to get to this. Skip the middleman!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors