Forum Discussion
Fiscal Year
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
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)
4 Replies
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
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.
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)Thank you so much Vijay_A_Verma this solution worked perfectly 🙂
