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 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'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

8 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    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

    • Kinsey's avatar
      Kinsey
      Advocate III
      9 years ago

      Switch can also do the same:

       

      SWITCH(TRUE(),

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

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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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!

  • 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