Converting Fiscal date to calendar date

[Johnweet]

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 

[Johnweet · Accepted solution]

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]

Hope this video helps:

https://www.youtube.com/watch?v=ef-KmCB-qXY

[Johnweet]

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.