Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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])

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.