cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hemantsingh Member
Member

Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-2016

Hi there,

 

I am trying to create a calculated column i.e financial year for my datekey table. I need to mark the financial year as per the date in date column. As soon as date changes to 01-04-2014 the financial year column gets an entry like 2014-2015. For dates lesser 01-04-2014 it should have enteries like 2013-2014.

 

Looking forward to some fruitful formula to get the same.

 

regards,

Hemant

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
sdjensen Senior Member
Senior Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

It appears that the DAX DATEADD formula will only return a result if the date calculated exist in the data, if I add 2009 to the data when the value will also be returned for 01-04-2009 to 31-03-2010. I don't have a solution for this at the moment. I would never use DAX to add columns like this to my model I would always build this in the query editor and the formula created there works.

/sdjensen

View solution in original post

sdjensen Senior Member
Senior Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

@hemantsingh -  You could try this DAX formula instead if you need to add the column using DAX:

IF(
	MONTH( Period[Date] ) < 4,
	YEAR( Period[Date] ) -1,
	YEAR( Period[Date] )
) 
& "-" &
IF(
	MONTH( Period[Date] ) < 4,
	YEAR( Period[Date] ),
	YEAR( Period[Date] ) +1
)

 

/sdjensen

View solution in original post

5 REPLIES 5
sdjensen Senior Member
Senior Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

I guess there is several different ways to calculate this, but this is my approach:

 

If you use Query Editor to create your date table, then this formula should work for you:

#"Added Fiscal Year" = Table.AddColumn(#"Name of Previous Step", "Fiscal Year", 
   each Number.ToText( Date.Year( Date.AddMonths( [Date], -3 ) ) ) 
   & "-" 
   & Number.ToText( Date.Year( Date.AddYears( Date.AddMonths( [Date], -3), 1 ) ) ) )

 

 

If you use DAX, then this formula should work:

Fiscal Year = 
YEAR( DATEADD( Period[Date], -3, MONTH ) ) & "-" & YEAR( DATEADD( DATEADD( Period[Date], -3, MONTH ), 1, YEAR ) 

 

I my case my dates table is called Period and the key colunm is called Date.

/sdjensen
hemantsingh Member
Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

Hi @sdjensen

 

  Thnks for taking out time for resolving my query. Further i tried ur dax formula for calculating the fiscal year in my required format but unfortunately, the result is somehow incorrect. 

 

   However, the formula is calculating & printing the required values in desired format but your dax formula is not printing any value for rows that has date range from 01-01-2010 till 31-03-2010 & for rows that has date column entry beyond 01-04-2021 till 31-12-2021 . The formula is correctly printing values from 01-04-2010 till 31-03-2021 in the datekey table.

 

For your info, I have made enteries in date column of datekey table starting from 01-01-2010 till 31-12-2021.

 

Awaiting your revert.

sdjensen Senior Member
Senior Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

It appears that the DAX DATEADD formula will only return a result if the date calculated exist in the data, if I add 2009 to the data when the value will also be returned for 01-04-2009 to 31-03-2010. I don't have a solution for this at the moment. I would never use DAX to add columns like this to my model I would always build this in the query editor and the formula created there works.

/sdjensen

View solution in original post

sdjensen Senior Member
Senior Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

@hemantsingh -  You could try this DAX formula instead if you need to add the column using DAX:

IF(
	MONTH( Period[Date] ) < 4,
	YEAR( Period[Date] ) -1,
	YEAR( Period[Date] )
) 
& "-" &
IF(
	MONTH( Period[Date] ) < 4,
	YEAR( Period[Date] ),
	YEAR( Period[Date] ) +1
)

 

/sdjensen

View solution in original post

hemantsingh Member
Member

Re: Trying to create a calculated column that has financial year like this 2013-2014,2014-2015,2015-

@sdjensen: indeed this way arround has worked perfectly for my query. it has successfully resolved my issue. also, I agree with you that dax dateadd function as of now takes only those year which are present in the column.

 

Regards,

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,286)