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

[hemantsingh]

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

[sdjensen · Accepted solution]

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]

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.

[hemantsingh]

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 · Accepted solution]

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 · Accepted solution]

@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
)

[hemantsingh]

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