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

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)

     

    • DAXRichArd's avatar
      DAXRichArd
      Resolver I
      6 years ago

      Thx

  • 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!