Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
@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 )
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.
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.
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.
@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: 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,
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.