Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |