Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I'm certain there's a more elegant (and correct) way of doing this, but I'm trying to create a calculated column which gives each date a corresponding fiscal year label e..g "FY16/17"
The formula I'm curently working on is
Financial Year = IF(DATESBETWEEN, Dates[Dates], DATE(2016,4,1) , DATE (2017,3, 31)) , "FY16/17",
IF(DATESBETWEEN, Dates[Dates], DATE(2017,4,1) , DATE (2018,3, 31)) , "FY17/18",
IF(DATESBETWEEN, Dates[Dates], DATE(2018,4,1) , DATE (2019,3, 31)) , "FY18/19", 0)
It keeps responding with an error message, can anyone guide me in the right direction?
Solved! Go to Solution.
Hi @WillTickel,
Based on my test, the formula below should also work in your scenario.
Financial Year = VAR fy = IF ( MONTH ( 'Dates'[Dates] ) <= 3, VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) - 1, VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) ) RETURN CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )
Regards
Hi @WillTickel,
Based on my test, the formula below should also work in your scenario.
Financial Year = VAR fy = IF ( MONTH ( 'Dates'[Dates] ) <= 3, VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) - 1, VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) ) RETURN CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )
Regards
I have written this code and am getting the error - Cannot convert value " of type text to type number.
Figured it out - the code works! However there was a blank cell in the [Date] column - it has over 5 years of data so yeah!
Switch can also do the same:
SWITCH(TRUE(),
Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),
YEAR([Date])&"-"&YEAR([Date])+1)
Thx
DATESBETWEEN is a function that returns a table, not a true/false as to whether or not a date falls between two dates
I think this should work for what you're trying to do:
Financial Year =
IF (
AND (
Dates[Dates] >= DATE ( 2016, 4, 1 ),
Dates[Dates] <= DATE ( 2017, 3, 31 )
),
"FY16/17",
IF (
AND (
Dates[Dates] >= DATE ( 2017, 4, 1 ),
Dates[Dates] <= DATE ( 2018, 3, 31 )
),
"FY17/18",
IF (
AND (
Dates[Dates] >= DATE ( 2018, 4, 1 ),
Dates[Dates] >= DATE ( 2019, 3, 31 )
),
"FY18/19",
0
)
)
)
Hope this helps,
David
Could you post the error message?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.