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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Johnweet
Helper I
Helper I

Converting Fiscal date to calendar date

Our Fiscal year runs from October 1st through to Sept 30th.  I receive a monthly report that just contains the month in full as text.  I have several years worth of reports in a folder.  I bring all the reports into PowerBI by importing all files in the folder.  Because the Fiscal year is in each file name I can create a column that shows Fiscal year.  I now have one complete file that shows the Fiscal year in one column and the month in another.  I need to convert those two columns in to a date field to be able to compare with other data.  The trouble is of course if the row says Fiscal year 23 and the month is October then the actual date is Oct 2022.  I don't want to manipulate the data in the spreadsheet input as I want the process to be automatic.

 

Any ideas 

1 ACCEPTED SOLUTION
Johnweet
Helper I
Helper I

I managed to solve it in the end by creating a conditional column that subtracts 1 from the year if the month is Oct, Nov or Dec.  I then combined the Day, Month & Year column into a date column.  Not the prettiest solution I'm sure but it seems to work.

 

= Table.AddColumn(#"Removed Columns2", "Custom", each if [Month] = "October" then [Fiscal Year]-1 else if [Month] = "November" then [Fiscal Year]-1 else if [Month] = "December" then [Fiscal Year]-1 else [Fiscal Year])

View solution in original post

3 REPLIES 3
Johnweet
Helper I
Helper I

I managed to solve it in the end by creating a conditional column that subtracts 1 from the year if the month is Oct, Nov or Dec.  I then combined the Day, Month & Year column into a date column.  Not the prettiest solution I'm sure but it seems to work.

 

= Table.AddColumn(#"Removed Columns2", "Custom", each if [Month] = "October" then [Fiscal Year]-1 else if [Month] = "November" then [Fiscal Year]-1 else if [Month] = "December" then [Fiscal Year]-1 else [Fiscal Year])

Padycosmos
Solution Sage
Solution Sage

It deosn't I'm afraid.  This shows how to convert Calendar year to Fiscal year.  I'm trying to go the other way.  I couldn't figure out how to reverse this.  I have  a column that says Fiscal year is 2023 and a column that says October.  The result needs to show Oct 22 and not october 23. Tried doing it with an IF statement, if Oct, Nov or Dec return (year -1), if any other month return the (year).  That means when I combine the columns I get Oct 2022 and Jan 2023.  I couldn't get this to work.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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