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.
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.
@@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.
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
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.
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.
Thanks, Thanks to Mr.Chandeep through whom we got to know you.
Excellent proposal. Highly demanded by companies. Thank you very much, Mark.
Thanks Ivan 😀
Lots of love from Bharat that is India
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 😀
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!
Thank you so much for sharing your knowledge
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.
WOW, WOW, WOW, very many thanks.
You are welcome! 😁
Brilliant😀
Thanks 😀
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 ruclips.net/video/lEa4ZiucYsI/видео.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.
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
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.
How could I get fiscal year to show as 2022-23 for example?
Change to text and use text formulas to generate the text.
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.