Convert Dates to Fiscal Periods with Power Query - Better than Formulas!
ฝัง
- เผยแพร่เมื่อ 7 ก.ย. 2024
- 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.
Download the Excel file here: www.myonlinetr...
Learn Power Query: www.myonlinetr...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy
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?
Thanks for watching, Peter! I didn't mean that Power Query was easier for the user, I meant it was easier for Excel to calculate in Power Query before loading to Excel. I obviously didn't make that clear! Thanks for sharing your LET solution. Can't say I've ever used QUOTIENT...I usually just use INT the function, so will have to keep it in mind for future.
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 😊
Thanks for sharing!
Why didn't I have this video 2 weeks ago when I needed it! So awesome.
Better late than never :-)
Love your tutorials Mynda, keep up the great work 👍🏻
Thanks so much, Graham!
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 Mandy, Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Another amazing tutorial.God bless you Mynda for sharing.
Thank you! 😊
Fantastic Mynda. All doubts resolved! Thank you!!!
Glad it was helpful, Ivan!
This was wonderful and easy to follow. Thanks so much!
Glad it was helpful!
This is exactly what I was looking for...thanks for the video
Glad I could help!
Very impressive, as always! Keep up the good work!
Thanks, will do :-)
PQ is great for these date transformations!
Glad you agree, Doug!
Power Query is best for such date transformation. Thanks for sharing content.
Glad you agree, Lalit :-)
Another amazing tutorial.Thank you Mynda.
Thanks so much, Alex!
Hi Mynda. Awesome tutorial.. as always. Thanks for sharing your great knowledge! Thumbs up!!
Thanks so much, Wayne!
Very good video, Mynda - Have a great weekend - Greetings from Germany
Thanks so much, Heiko! Hope you’re having a great weekend too 😊
congrat... you save me!!! Brazil appreciated!!...
So glad I could help, Lucas!
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? :-)
:-D love the VLOOKUP solution, Peter! I love that there are so many ways to solve challenges in Excel. We're only limited by our imagination.... and knowledge.
Hi Mynda!Great Tutorial,Really Helpful Tips...Thank You :)
Great to hear, Darryl! Thanks :-)
Awesome! Exactly what I needed! Thank you so much!
Pleased to hear that, Patrick!
Got lost from 3:16-4:20 but will keep up rechecking. Thank you
Here's a tutorial on writing 'if' statements in Power Query that will hopefully help: www.myonlinetraininghub.com/power-query-if-statements
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.
Ah, yes, Power Query is not really available to for Mac. I say 'not really' because it's still under development so functionality is limited. See this video on formulas for fiscal years: th-cam.com/video/jQ1l1cqmj7Y/w-d-xo.html
And if you want to use Slicers, then this video: th-cam.com/video/z2B_wM5Z_bE/w-d-xo.html
Very cool Mynda!
Cheers, Chris :-)
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.
Hi Shaun, so pleased you're enjoying my tutorials. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great video! Thank you.
Glad you liked it!
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.
Cheers, Jerry. In the Periods_454 query you can change the data type to 'type text' for the Fiscal Quarter column, but if you're not confident in editing the M code, you may find it easier to add the Qs to the quarters in the lookup table before loading, then the data type will correctly be formatted as text.
Thank you kind lady, straya ftw
My pleasure, Omut!
Great video, what if i want the fiscal year to say FY-20 instead of FY19/20?
You can use this code in the Power Query technique:
= if [Month] < 7 then "FY-" & Text.End(Number.ToText([Year]),2) else "FY-" & Text.End(Number.ToText([Year]+1),2)
Excellent info..👍
So nice of you, Suresh!
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.
Awesome to hear. The sorting video is coming out next week! 😉
Thanks
Many Thanks Mrs. Mynda ♥
You're welcome, Hazem!
@@MyOnlineTrainingHub Many thanks for caring to Reply on us
@@MyOnlineTrainingHub Thanks Mynda ♥
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?
Thank you! See this post for fiscal weeks: community.powerbi.com/t5/Desktop/Custom-Fiscal-Year-Calendar/m-p/85228
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
Replace the formula in the step called 'Added Conditional Column2' to this: = Table.AddColumn(#"Added Conditional Column1", "Fiscal Year", each if [Month] < 7 then Text.End(Number.ToText([Year]-1),4) & "/" & Text.End(Number.ToText([Year]),4) else Text.End(Number.ToText([Year]),4) & "/" & Text.End(Number.ToText([Year]+1),4))
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?
Great to hear, Richard! Power Query is enabled by default in Excel 2013 onward. However, they don't need to use Power Query once you've loaded the data to the Excel worksheet or Power Pivot.
Hi Mynda, Thank you for the easy to follow tutorial. Question, on the 454 method, is there a way to extract fiscal months?
You'd have to add them to the lookup table.
@@MyOnlineTrainingHub, thank you for responding :) I run into another problem. So my company's 445 calendar is a bit odd, in that our months don't end on the last days of the calendar month except for December. Our calendar is more like a 345 (for the first quarter), 445 for the second and third quarter, and 446 for the last quarter. How would I create a calendar with such a scenario? Thank you very much for your help.
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)?
Thanks, Terry! Power Query correctly calculates the number of days between dates for leap years. You'd then need to convert that to years, as it doesn't have this option in the 'age' calculation.
@@MyOnlineTrainingHub Thanks for your response Mynda , that’s what I thought. Somewhat confusing, I think, to have an ‘age’ calculation in Power Query that doesn’t return an accurate result. Keep up the great tutorials 👍
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
My pleasure, Krish! You can use Power Query to eliminate data you don't need in your file before importing. You can also load the data from Power Query to Power Pivot, which is typically more efficient than loading the data into the Excel grid. From Power Pivot you can create PivotTables to analyse and summarise your data. More on Power Pivot here: www.myonlinetraininghub.com/power-pivot-course
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.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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 ?
Not sure I understand, but perhaps this is what you mean: www.myonlinetraininghub.com/change-pivottable-aggregation-methods-using-excel-slicers
@@MyOnlineTrainingHub tq mam for ur reply and I will go through the link u given... And by using this Dax measure I am getting current year previous month but my requirement is previous year same month
Value in Matrix =
IF (
SELECTEDVALUE('Table'[Date]) = DATE(YEAR(MAX('Slicer'[Date])),MONTH(MAX('Slicer'[Date]))-1,1),
SUM ( 'Table'[Sales] ),
BLANK ()
)
I know this is an old thread but does this custom column for FY handle more than one fiscal year of date data?
Yes, you can modify it for as many years as required.
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)
Hi Jaimal, Power Query is type sensitive, which is why the error says it cannot apply operator + to types Text and Number. In other words, it cannot add a number to text. You need to check the data types for the fields you're wanting to add and fix them in the prior step, or fix them in the formula. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
👍👍👍
Thanks!
I tried to do this and “token literal “expected comes up under “else”. Help!!
It means the word 'else' is expected in an if statement e.g. if this is true then that else this
Complecates