cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## 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

1 ACCEPTED SOLUTION
Super User

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 4
Super User

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)``
Helper I

Thank you so much @Vijay_A_Verma this solution worked perfectly 🙂

Super User

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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Helper I

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.