Fiscal Year

[Dev-13]

Hi 

I have created a date table in power query using list.dates 

I want to add in the financial year to this table in the format yyyy/yy e.g. 2018/19

I have the calendar year, calendar month, fiscal year, fiscal month and fiscal quarter.  The fiscal year is formatted to yyyy e.g. 2018 but doesn't show the format 2018/19 if that makes sense.

Thanks

[Vijay_A_Verma · Accepted solution]

Use this formula in a custom column where 3 means FY finishes in March month. Similarily say your financial year finishes in Jun month, replace 3 with 6..If it finishes in Jul, replace 3 with 7.

You will need to replace [Year] and [Month] fields appropriately by calendar year and calendar month

Text.From([Year]-Number.From([Month]<=3))&"/"&Text.End(Text.From([Year]+Number.From([Month]>3)),2)

[Dev-13]

Thank you so much @Vijay_A_Verma this solution worked perfectly 🙂

[Anonymous]

Assuming your year column is Int64 type (which it should be), if all of your years are this year/next year (like if all of your 2018 or 2018/19, for example, then you can just add a column like

Table.AddColumn(PriorStepOrTableName, "Financial_Year", each Text.From([Year]&"/"&Text.From([Year]-1999), type text)

--Nate

[Dev-13]

Hi Nate

Thanks for your quick reply. My year column is a whole number. I can't see how to change it to int64. Unfortunately when I added a new custom column and added in the formula, it resulted in an error.