Forum Discussion

WillTickel's avatar
WillTickel
Frequent Visitor
9 years ago
Solved

Fiscal Year Calculated Columns

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 ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi WillTickel,

     

    Based on my test, the formula below should also work in your scenario.:smileyhappy:

     

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
9 years ago

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