Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jhaldane
Frequent Visitor

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

2 ACCEPTED SOLUTIONS
AlB
Community Champion
Community Champion

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

View solution in original post

AlB
Community Champion
Community Champion

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

View solution in original post

7 REPLIES 7
jhaldane
Frequent Visitor

Thank you. Happy Days

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

FY year.png

AlB
Community Champion
Community Champion

@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
Community Champion
Community Champion

Hi @jhaldane

 

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

AlB
Community Champion
Community Champion

@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
Frequent Visitor

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

 

Capture.PNG

 

AlB
Community Champion
Community Champion

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.