Forum Discussion

jhaldane's avatar
jhaldane
Frequent Visitor
7 years ago
Solved

Adding a column to show Fiscal Year

Hello team BI, I have a calendar table. See below. I now need to have April - March as the 2017 FY year.... Similaily after that, 2018 FY, 2019 FY Please can you assist me with the code:   Calen...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    jhaldane

     

    Oh you mean a new column with the FY. Something like this? April 2016-March 2017 is FY 2017, right? Otherwise update the code in the last column. I added a month number too to simplify the code.

     

    Calendar =
ADDCOLUMNS (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2016, 4, 1 ), DATE ( 2020, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "MonthNameShort", FORMAT ( [Date], "mmm" ),
        "MonthNumber", MONTH ( [Date] ),
        "Quarter", "Q" & FORMAT ( [Date], "Q" ),
        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
    ),
    "FY", IF ( [MonthNumber] >= 4, [Year] + 1, [Year] )
)
  • AlB's avatar
    AlB
    7 years ago

    jhaldane

    Yeah, you need to copy the code exactly as I wrote it for it to work. From what I see in your image, it's not the same. You're leaving the outer ADDCOLUMNS out, for instance.  

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

jhaldane

 

Oh you mean a new column with the FY. Something like this? April 2016-March 2017 is FY 2017, right? Otherwise update the code in the last column. I added a month number too to simplify the code.

 

Calendar =
ADDCOLUMNS (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2016, 4, 1 ), DATE ( 2020, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "MonthNameShort", FORMAT ( [Date], "mmm" ),
        "MonthNumber", MONTH ( [Date] ),
        "Quarter", "Q" & FORMAT ( [Date], "Q" ),
        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
    ),
    "FY", IF ( [MonthNumber] >= 4, [Year] + 1, [Year] )
)
jhaldane's avatar
jhaldane
Frequent Visitor
7 years ago

Hello, 

Thanks for suppoorting

I keep on receiving the below error.

- The MonthNumber is now visible 

- The FY year seems to show the error.

 

Thanks

J

 

 

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    jhaldane

    Yeah, you need to copy the code exactly as I wrote it for it to work. From what I see in your image, it's not the same. You're leaving the outer ADDCOLUMNS out, for instance.