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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.