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:

 

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2016,4,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

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.  

7 Replies

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

    Hi jhaldane

     

    I don't quite understand what you need. One table for each FY described?

    • 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

         

         

    • jhaldane's avatar
      jhaldane
      Frequent Visitor
      7 years ago

      Hi

      I would like my graph to show data only for that FY year. i.e.. 2018 = April 2017 to March 2018

      The below shows from Jan - December

      Thanks

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

        jhaldane

        Check what year the months shown belong to. Probably they are the correct ones (mix of 2017 and 2018) but just ordered from Jan to Dec.