Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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 April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
67 | |
67 | |
42 | |
42 |
User | Count |
---|---|
46 | |
40 | |
28 | |
26 | |
25 |