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

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

Reply
Dev-13
Helper I
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
Vijay_A_Verma
Super User
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)

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
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)

Thank you so much @Vijay_A_Verma this solution worked perfectly 🙂

watkinnc
Super User
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!!

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.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors