Fiscal Year Calculated Columns

[WillTickel]

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?

[v-ljerr-msft · Accepted 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

[Anonymous]

I have written this code and am getting the error - Cannot convert value " of type text to type number. 

Fiscal Year =

VAR FY =

IF (

MONTH ( Table1[DATE] ) <= 3,

VALUE ( FORMAT ( Table1[Date], "YY" ) ) - 1,

VALUE ( FORMAT ( Table1[Date], "YY" ) )

)

RETURN

CONCATENATE ( "FY ", FY + 1 )

Please help!

[Anonymous]

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!

[Kinsey]

Switch can also do the same:

SWITCH(TRUE(),

Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),

YEAR([Date])&"-"&YEAR([Date])+1)

[DAXRichArd]

Thx

[WillTickel]

Many Thanks @v-ljerr-msft. It worked perfectly!

[dedelman_clng]

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

[RMDNA]

Could you post the error message?