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

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?

Another amazing tutorial.God bless you Mynda for sharing.

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 😊

Very impressive, as always! Keep up the good work!

Hi Mynda. I have some words to eat! I worked through the Power Query and was fascinated by the way you used the outer join to combine two dissimilar datasets in order to achieve the equivalent of an approximate lookup. I would have claimed that ordered lists such as time sequences should be treated as array problems and not list processing, yet you managed the task with some elegance! That said, INDEX/XMATCH returned all the values with a single array, XLOOKUP was elegant if one is prepared to accept a single row formula copied down. The next shock for me was that VLOOKUP, a function that I have despised for years, also did a pretty good job = VLOOKUP(Table2[@Date], Periods_454, {1,2,3,4}) How am I supposed to hang on to my prejudices when you insist on demonstrating other possibilities? :-)

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

Many Thanks Mrs. Mynda ♥

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.

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.

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.

I tried to do this and “token literal “expected comes up under “else”. Help!!

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.

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

Great tutorial Mynda - Thanks. BTW do you know if is it possible to calculate age (in years) in Power Query that correctly accounts for leap years (like DATEDIFF)?

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

PQ is great for these date transformations!

HI mynda my formula is correct and when i click ok, i receive some errors saying "We cannot apply operator + to types Text and Number." (my formula is right though)

Mynda, I have a question, my Purchase orders(PO) spread over financial years, mostly over two but a few of them for 3 , 4 or 5 years also, I want to calculate the number of dates each PO is active in each financial year and then accordingly assign spend to each year assuming spend is evenly spread for each day of the PO, please guide with this query.

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.

Thank you very much for your videos. Is there a way to reduce files size using power query? I am having trouble to share 100mb files

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

Hi Mynda, this is exactly what I have been looking for. If I use these to create a model for my users, will they need Power query enabled to use it?

Hi Miranda, this is very informative. Always loved watching your videos. Can you please let me know how I can create the week number in power query where week 1 starts in 1st July?

congrat... you save me!!! Brazil appreciated!!...

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

I know this is an old thread but does this custom column for FY handle more than one fiscal year of date data?

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

Hi Mynda. Awesome tutorial.. as always. Thanks for sharing your great knowledge! Thumbs up!!

Complecates

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

Hi Mynda, Thank you for the easy to follow tutorial. Question, on the 454 method, is there a way to extract fiscal months?

Very good video, Mynda - Have a great weekend - Greetings from Germany

Hi mam, in matrix visual based on the selection in the slicer I need to see the change in the column names(headers) dynamically.. can we achieve this by using Dax measure ?

Hi Mynda!Great Tutorial,Really Helpful Tips...Thank You :)

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

👍👍👍

Great video! Thank you.

Thank you kind lady, straya ftw

Awesome! Exactly what I needed! Thank you so much!

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

Another amazing tutorial.Thank you Mynda.

Excellent info..👍

Very cool Mynda!