cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)