Adding a column to show Fiscal Year

[jhaldane]

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" )
)

[AlB · Accepted solution]

@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]

Thank you. Happy Days

[jhaldane]

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]

@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. 

[AlB]

Hi @jhaldane

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

[AlB · Accepted solution]

@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]

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 · Accepted solution]

@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.