Convert Dates to Fiscal Periods with Power Query - Better than Formulas!

It's more efficient for Excel to add columns for your fiscal periods with Power Query than it is with formulas. In this video I'll cover both fiscal periods that start at the beginning of a month and those, like a 4-5-4 calendar, that don’t.

[peterbartholomew]

As always I enjoyed your video and agree that the best time for cleaning and sorting data is before it ever gets to the Excel workbook.
That said, I wouldn't regard the processing involved in Power Query as in any way trivial. I am not sure that a user that struggles with a VLOOKUP is going to do so much better distinguishing their left outer join from an inner join. You are now so familiar with the environment that you handle the advanced editor with flair and fluency but I assure you I am far more laboured.
For the Aussie FY, I reverted to a single formula
= LET(
oldFY?, MONTH(date) < MONTH(startFY),
FiscalMonth, 1 + MONTH(date) - MONTH(startFY) + 12*oldFY?,
Quarter, 1 + QUOTIENT( FiscalMonth-1, 3 ),
FY, YEAR(date) - oldFY?,
CHOOSE({1, 2, 3}, FiscalMonth, Quarter, FY ) )

It just got me to the point of deciding that accountancy and tax are incomprehensible. In the UK our fiscal year starts on 6 April, so how many days are there in March? Or is April 3 part of month 13?

[mandywu-fttalladventure]

Mynda, thanks for the awesome tutorial. I do have a more complex question.

How would one solve the fiscal calendar problem when it is a moving target? Here's an example using 2024 calendar, a company's fiscal year starts in Feb and each quarter consist of 13 (4+4+5 weeks) + 1 weeks. The one week is overlapped between quarters. So say, if we are in Feb/Q1, that quarter would start on 1/29/24 (week 1) and ends 5/5/24 (week 14). Next quarter, Q2, it starts on 4/29/24 (week 14) and ends 8/4/24 (Week 27) and so forth.

[Metzanine]

I seem to remember getting fiscal years by using parameters in power query. Much quicker and easier, but not dynamic. But if you list out several years into the future that's not a problem 😊

[_Bike_is_Life]

Why didn't I have this video 2 weeks ago when I needed it! So awesome.

[KimCryns]

Again, Amazing information and explanation. What is the correct order for the quarters if your fiscal year starts in April? I have Month is greater than or... 10 Q3, 7 Q2, 4 Q1 else Q4. Only the first 3 months are in one year from the budget tab, the rest are in the following year. (I have changed the FY to < 4).

[Orions_Journey]

Great video, what if i want the fiscal year to say FY-20 instead of FY19/20?

[dennisBZC]

Hi Mynda, your videos are really good and I’m so grateful for you to share your knowledge. I have seen examples with year() + month() instead of PowerQuery, which I’m not familiar with. I am not even sure I know how to find it. Doesn’t help when Mac version of Excel uses different commands even when it is still Microsoft 365.

But on this, how would you tighten the filter to get the tax year based on days, rather than just months. For example, UK fiscal years go from 6/4/2024 to 5/4/2025?

I would be v grateful if you could explain that.

[alexsonga]

Another amazing tutorial.God bless you Mynda for sharing.

[grahamparker]

Love your tutorials Mynda, keep up the great work 👍🏻

[IvanCortinas_ES]

Fantastic Mynda. All doubts resolved! Thank you!!!

[Ola-iuzc]

Hi Mynda, this will solve my UK calendar issue which starts in April and ends in March. My question is how do I sort my table to start from April and not January. Thanks.

[dataengineers]

Got lost from 3:16-4:20 but will keep up rechecking. Thank you

[setslife]

Great video, realy helpful, I have one question, if we want to have fiscal year 2022/2023 instead of FY22/2023, what do i need to change, i tried a few things but always showing an error as token Eof Expected

[DougHExcel]

PQ is great for these date transformations!

[jerrydellasala]

Great technique & video!
In the Periods_454 query, the Fiscal Qtr column data type was changed from Any to Whole Number, however if you then change the values in that table from numbers (1-4) to Quarters (Q1-Q4), the query has an error. Removing "{"Fiscal Qtr", type text}" returns the column data type to "any" which allows it to be a number if no Q is present, or text when there is. Changing the data type to Text forces a single digit entry to be loaded as Text.

[johnandrawous]

This was wonderful and easy to follow. Thanks so much!

[ammarwasif]

This is exactly what I was looking for...thanks for the video

[shauncoombe]

Hi Mynda, your tutorials are just on another level. That said I'm trying to achieve something a little unique and does not quite fit into the boxes of the tutorials you've given or I simply don't know how to apply them correctly...probably the latter. Can you tell me if you offer private hours and if so could you advise what you need in return? Please accept my apologies for this public request but I did not have any other contact options available to me.

[excelhouse]

Power Query is best for such date transformation. Thanks for sharing content.

[alexsonga]

Another amazing tutorial.Thank you Mynda.