Add a Fiscal Month, Quarter or Year Column in Power Query | Excel Off The Grid
Вставка
- Опубліковано 15 лип 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/power-que...
★ About this video ★
Over the last few months, I've been asked several times how to add a fiscal year, fiscal quarter, or fiscal month column in Power Query. So I decided to note down the method so I don't have to keep reminding myself how I did it last time.
The good news is that it's reasonably straightforward. So let's see what we need to do.
CONTENTS
0:00 Introduction
0:31 Example Data
1:12 Fiscal Month
3:13 Fiscal Year
4:48 Fiscal Quarter
6:11 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Thanks, Thanks to Mr.Chandeep through whom we got to know you.
Thank you so much for sharing your knowledge
Excellent proposal. Highly demanded by companies. Thank you very much, Mark.
Thanks Ivan 😀
Wooow BRILLIANT ! Awesome delivery in 6mins. Thank you soooo much
Thank you- somuch easier than what I’ve been doing!
Glad it helped! 😀
very helpful. Thanks so much Mark for sharing your knowledge
Glad it was helpful!
Hi Mark, It was really an excellent way to arrive at Fiscal year and Month. The last one to calculate Fiscal quarter was awesome and brilliant 😊
Sometimes it just takes a slightly different thought process 😀
WOW, WOW, WOW, very many thanks.
You are welcome! 😁
Lots of love from Bharat that is India
Hey Mark, this is how I do it.
Fiscal Month = Date.Month(Date.AddMonths([Date], -3))
I've used that way previously too, and I suspect it calculates a little faster as there is no if/then/else logic involved.
However, I went for an approach that applies a consistent logic between months and years, as I think it's easier to understand for those coming from Excel.
Brilliant😀
Thanks 😀
Do you have a clever way to add fiscal week number with the same example of April 1 start?
Hi, I need the last Thursday of the month to be considered as month cut-off. The remaining days of that month goes to next month. Any solutions please..
Apparently I live in the GCC
Hey Mark, Very useful, thanks. So if I did need to modify it slightly such that the calendar month and fiscal month don't map equally i.e. our fiscal month or "period end" as we call it is usually a few days before or after the actual calendar month end, do you have a clever solution for that scenario? Have you already done a video on it ? My current "clunky" solution is a big long lookup table but I'm sure there are better ways. I would appreciate any suggestions. Thx Ben
If it is always a consistent date in the month, then you could adjust it (e.g always 18th of month, or always 4 days before the calendar month end).
But anything else will requires a different approach which probably involves a manual table containing month end dates.
@ExcelOffTheGrid Sadly, it's not consistent. We have a 4-4-5 weeks per period pattern that needs adjustment once every 6 years into a 4-4-6 quarter, resulting in a 53 week year to try and keep in sync with the calendar.
@@bengiblett2209 Maybe this might help if you haven't already found a solution ua-cam.com/video/lEa4ZiucYsI/v-deo.htmlsi=96iec5vsoJv_q9vE
Hi Mark. At 4:27 for Fiscal Year calculation, I feel if Date.Month(Date)
If you do that, you’ll get the wrong year.
That’s why the logic is always a little trickier than we might expect it to be.
@@ExcelOffTheGrid Thanks Mark for your reply. It seems that I have been wrong for many years 😢 . Take date "15 March 2023" for example, although I know it is in the last fiscal month (UK finance year), according to the formual in the video, it belongs to financial year 2023. I had thought it belongs to financial year "2022", not 2023. I thought finanical year 2023 starts from 1 April 2023. Thank you for your correction.
@@kebincui Usually it’s based in the year of the year end date.
To avoid the confusion many companies use FY23/24.
But it’s possible that some might apply a different approach, but you can normally build the logic for any scenario.
How could I get fiscal year to show as 2022-23 for example?
Change to text and use text formulas to generate the text.
I hate all those #"" in the M Code and typically rename every step removing spaces at least, and prefer them to be more descriptive. Also, rather than having to adjust the FY End month in multiple places if I need this query again, rather than missing an entry I prefer using a variable - just as one would use the value in a cell rather than hard coding a value in a Worksheet. Towards those ends I adjusted the M Code manually adding the line of code for the variable in the Advanced Editor immediately after the Source line. Doing it that way bypasses the PQ Editor getting confused over step sequence (although once there it can be changed without using the Advanced Editor):
let
Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
FYEndMonth = 3,
ChangedDateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
AddedFiscalMonth = Table.AddColumn(ChangedType, "Fiscal Month", each if Date.Month([Date])
Hi Jerry - I can agree with all your points there. I also hate the #"" notation, but I'm more relaxed about whether I purposefully go back and rename the steps. I would advise always renaming, but I'll get lazy for a simple solution.
In the real world, I would include my month end date in a cell in Excel and load it as a parameter, but that's well outside the scope of this video.
Interesting, but how do you determine the fiscal month in a 4-4-5 fiscal calendar?
I’ll try to cover that in a future video.
My guess is that you don’t actually have a 4-4-5 calendar.
You have a 4-4-5 normally, but occasionally 4-4-6, or 4-5-5 when needed.
This means that you’ll always be reverting to some form of manual table, as the logic isn’t clear enough.