Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
99 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |