Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0215 PQ Calendar Table.zip
Thanks a lot, Mark - i've been maintaining an 'almost automated 4-5-4 calendar table' in Excel for years, didn't think about automating it using M-code. Some good techniques picked up to use elsewhere 🙂
And I think an “almost automated 4-5-4” in Excel is pretty good option. It’s easy to maintain, and probably takes less than 5 mins per year to update. 👍
Great video on setting up a Calender table, would you be able to advise on how to create a column for UK Tax year? e.g. 2023/24 as the tax year runs from the 6th April to the 5th April the following year
This is a great explanation Mark, thank you! I ran into one issue when creating this myself for a non-standard calendar that my company uses - it's a custom variance of the 4-4-5 approach. I defined my fiscal period inputs separately and brought them in as you did, but I noticed that if the start or end date didn't match to a date listed specifically in the fiscal period inputs table, then I'd end up unable to map some rows, as there was nothing to fill up or fill down. Is there an alternative approach that can be taken for these? As a workaround I've set the start and end date so they have to match to a date in the fiscal period inputs table, but ideally I'd like to be able to input any start or end date.
In the example data I use, the first date in the list of non-standard periods is earlier than the first date in my data set. Otherwise, it misses dates (as you've experienced too). You can't be flexible with a 4-4-5 calendar due to leap years, the 53-week year, and management whims, there is too much variability. So, you need to use whatever methods you can to force it to work.
In my opinion, a date table is the same as every other dimension Table. Therefore we should load it into the data model in the same way. A PQ calendar table is also more dynamic (I.e creating dates based on the data). Ultimately, there is probably little difference. But I prefer the PQ option.
@@ExcelOffTheGrid thanks for the answer 🙂 yeah I have had issues in the past sometimes creating the date table in power pivot with it picking up a date column that I actually want it to ignore. I'd never considered building the date table using power query so I'll definitely give this a go! Thanks again.
I have an issue I’ve been trying to solve and I’m hoping you can provide a direction. I have a table of employees with a start date and an end date. Current employees don’t have an end date. I need to calculate the number of active employees per month for the last 3 years. I’ve tried to use a join the data to a date table and use sumx and collect as a measure to calculate it, but since there isn’t a date for each month that there been employed it’s only counting each employee the first month they’re employed. Any thoughts?
Nice Information! How can we calculate Fiscal Year over Year % growth ? example I have 4 Fiscal years to compare % growth in Volumes for more than 300 customers and we have info of volumes for every moth for 4 FY years.
We would just use Date.QuarterOfYear instead of Date.Year or Date.Month. I've worked in some large companies and interestingly, quarter has never been a requirement. So, it never really comes to my mind; but I know others use them a lot.
Thanks for the video ! I personally perfer the smaller m-code { Number.From( StartDate ) .. Number.From( EndDate ) } I hope the next video is about Time Table
Yeah, that works. I don't have a big preference wither way. In our membership, we've got some custom functions in the Functions Library to deal with Time Tables and grouping into time segments. But at the moment I've not got any plans for a video.
Thank You for Your Video, Mark. Gratitude! I will be grateful if You respond to the following question: Q) How did You create the dataset for the Period End Dates query? If it was originally created in MS Excel using formulas . . would be wonderful if You share the formulas? It will be very helpful Thank You!
Hi Mark, I was able to create the 4-4-5 Weeks dates using the following formula: =SCAN("31-12-2023", SEQUENCE(16), LAMBDA(a, b, IF(MOD(b, 3) = 0, a + 35, a + 28))) I will be Grateful if You share Your method to generate 4-4-5 Weeks dates. Thank You!
It was originally created in Excel. However, the issue is the 53 week year. So the pattern changes and is affected by leap years. Also, organisations don’t stick to fixed rules. Therefore, there isn’t a guaranteed formula. Whatever you do, it will need to be manually checked and adjusted.
Hi Mark. I really enjoyed the video and it will come in very useful. I see your dates in the calendar are in UK format. Can you help me an issue I appear to have suddenly noticed only this week? When I have dates in an Excel sheet or csv file in UK format they are now always pulling into Power Query in US format. I have tried using the Local option, but as soon as I change Type back to Date I then get an error for any date with the day greater than 12. This was working fine just last week. My Excel date setting is UK and so is my Windows settings. I can't think of any other reason this has started happening. Regards, Alan
@@ExcelOffTheGrid It is now happening on all workbooks. I ended up trying a Quick Repair of Office 365 and it won't finish the repair, so possibly something in my system has become corrupt. I'll try the usual repair/restore options over the weekend and report back as this behaviour is odd. Sorry Mark, I realise this isn't a help desk, but I thought I was missing a setting somewhere. Regards, Alan
@@ExcelOffTheGrid Just to follow up. After doing a system restore, all is working again and I am getting the correct date format. I can delete my posts if you wish. Regards, Alan
HOW DID YOU CREATE THE Source CODE FOR THE TWO TABLES? DO YOU HAVE A FUNCTION YOU COULD SHARE? PLEASE???!!! Two comments. The start date must always be January 1 of the first year and the end date must always be December 31 of the last year. To get this I chose to use: StartDate = #date(Date.Year(List.Min(Transactions[Date])),1,1), EndDate = #date(Date.Year(List.Max(Transactions[Date])),12,31), Also, after the Fill Up operation, it's a LOT easier to filter null in the Year column than the Date column, especially for a calendar for more than one year. Still a valuable video!
The Tables were created with Enter Data. But could come from anywhere. You can find them in the Example file - which is available in our free Insiders program.
We use 4,4 ,5 - your solution is good for a single year, not so good with a large date range - Had hoped it was going to use equivalent of Excels weeknum and type 21 - that's been stumping me a bit
@@ExcelOffTheGrid apologies Mark - I was thinking Leap years etc would throw it out and mean you would have to add the period end/start dates fairly manually, but i do now think it would work - thank you - sorry for jumping to wrong conclusion
Get an error EVERYTIME i try to enter this. I am using Office 365 Power Query. I have written it 4 times EXACTLY as you show and everytime I hit the DONE button I get this error "An error occurred in the ‘’ query. Expression.Error: The name 'Startdate' wasn't recognized. Make sure it's spelled correctly." Any idea, I give up on learninig if I get unexplained arrors all the time.
Power Query is case sensitive. So your variable is probably called StartDate, but then you’ve entered Startdate when you have tried to use it. That is my guess.
@@ExcelOffTheGrid FOUND IT. I didn't know it was case sensative. Once I fixed that it works. I have a lot to learn about PQ. But I plan on learning it. I can see where its power can be a GREAT addition to Excel. Thanks for the help.
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0215 PQ Calendar Table.zip
This has to be one of the best video presentations on Calendar Dates.
Thanks Mark.
Thank Anil 😁
The best video presentation on Calendar Dates!! Thanks.
Wow, thanks. I’m glad it was helpful!
Fantastic video as always! Your PQ vids have been a lifesaver for my occasional large data tasks, from a crawl to swift delivery. ⚡️👍🏾
That's great news. I'm glad to help.
Thanks a lot, Mark - i've been maintaining an 'almost automated 4-5-4 calendar table' in Excel for years, didn't think about automating it using M-code. Some good techniques picked up to use elsewhere 🙂
And I think an “almost automated 4-5-4” in Excel is pretty good option. It’s easy to maintain, and probably takes less than 5 mins per year to update. 👍
You are good, both in choosing the topic and make them easy to implement
Thank you
Thank you, that's very kind of you to say.
Awesome Level of teach and special thanks
Thanks - I'm glad you like it. 😁
Great video on setting up a Calender table, would you be able to advise on how to create a column for UK Tax year? e.g. 2023/24 as the tax year runs from the 6th April to the 5th April the following year
This is a great explanation Mark, thank you!
I ran into one issue when creating this myself for a non-standard calendar that my company uses - it's a custom variance of the 4-4-5 approach. I defined my fiscal period inputs separately and brought them in as you did, but I noticed that if the start or end date didn't match to a date listed specifically in the fiscal period inputs table, then I'd end up unable to map some rows, as there was nothing to fill up or fill down. Is there an alternative approach that can be taken for these?
As a workaround I've set the start and end date so they have to match to a date in the fiscal period inputs table, but ideally I'd like to be able to input any start or end date.
In the example data I use, the first date in the list of non-standard periods is earlier than the first date in my data set. Otherwise, it misses dates (as you've experienced too).
You can't be flexible with a 4-4-5 calendar due to leap years, the 53-week year, and management whims, there is too much variability. So, you need to use whatever methods you can to force it to work.
@@ExcelOffTheGrid Thanks Mark. That makes complete sense to me, and is what I feared! I'll continue with my workaround method instead.
Is there an advantage to creating the date table in power query rather than power pivot? Are there pro's and con's for one way over the other?
In my opinion, a date table is the same as every other dimension Table. Therefore we should load it into the data model in the same way.
A PQ calendar table is also more dynamic (I.e creating dates based on the data).
Ultimately, there is probably little difference. But I prefer the PQ option.
@@ExcelOffTheGrid thanks for the answer 🙂 yeah I have had issues in the past sometimes creating the date table in power pivot with it picking up a date column that I actually want it to ignore. I'd never considered building the date table using power query so I'll definitely give this a go! Thanks again.
I have an issue I’ve been trying to solve and I’m hoping you can provide a direction. I have a table of employees with a start date and an end date. Current employees don’t have an end date. I need to calculate the number of active employees per month for the last 3 years. I’ve tried to use a join the data to a date table and use sumx and collect as a measure to calculate it, but since there isn’t a date for each month that there been employed it’s only counting each employee the first month they’re employed. Any thoughts?
It sounds like you need to use CALCULATE, using the dates and blank to change the filter context.
@@ExcelOffTheGrid thank you. I’ll give that a shot.
Nice Information! How can we calculate Fiscal Year over Year % growth ? example I have 4 Fiscal years to compare % growth in Volumes for more than 300 customers and we have info of volumes for every moth for 4 FY years.
fantastic one! You could also have worked on Quarters for by CY and FY... that would of great use!!
We would just use Date.QuarterOfYear instead of Date.Year or Date.Month.
I've worked in some large companies and interestingly, quarter has never been a requirement. So, it never really comes to my mind; but I know others use them a lot.
Thanks for the video !
I personally perfer the smaller m-code
{ Number.From( StartDate ) .. Number.From( EndDate ) }
I hope the next video is about Time Table
Yeah, that works. I don't have a big preference wither way.
In our membership, we've got some custom functions in the Functions Library to deal with Time Tables and grouping into time segments. But at the moment I've not got any plans for a video.
Thanks Mark!
Thank You for Your Video, Mark. Gratitude!
I will be grateful if You respond to the following question:
Q) How did You create the dataset for the Period End Dates query?
If it was originally created in MS Excel using formulas . . would be wonderful if You share the formulas?
It will be very helpful
Thank You!
Hi Mark,
I was able to create the 4-4-5 Weeks dates using the following formula:
=SCAN("31-12-2023", SEQUENCE(16), LAMBDA(a, b, IF(MOD(b, 3) = 0, a + 35, a + 28)))
I will be Grateful if You share Your method to generate 4-4-5 Weeks dates.
Thank You!
It was originally created in Excel.
However, the issue is the 53 week year. So the pattern changes and is affected by leap years. Also, organisations don’t stick to fixed rules.
Therefore, there isn’t a guaranteed formula. Whatever you do, it will need to be manually checked and adjusted.
O.K.
Thank You for Your Response. Appreciate!
Very informative video
Glad you liked it 😁
Hi Mark.
I really enjoyed the video and it will come in very useful.
I see your dates in the calendar are in UK format.
Can you help me an issue I appear to have suddenly noticed only this week?
When I have dates in an Excel sheet or csv file in UK format they are now always pulling into Power Query in US format.
I have tried using the Local option, but as soon as I change Type back to Date I then get an error for any date with the day greater than 12. This was working fine just last week. My Excel date setting is UK and so is my Windows settings.
I can't think of any other reason this has started happening.
Regards,
Alan
Is it in on workbook or all workbooks?
@@ExcelOffTheGrid It is now happening on all workbooks. I ended up trying a Quick Repair of Office 365 and it won't finish the repair, so possibly something in my system has become corrupt. I'll try the usual repair/restore options over the weekend and report back as this behaviour is odd.
Sorry Mark, I realise this isn't a help desk, but I thought I was missing a setting somewhere.
Regards,
Alan
@@ExcelOffTheGrid Just to follow up. After doing a system restore, all is working again and I am getting the correct date format. I can delete my posts if you wish.
Regards,
Alan
Thanks for keeping me up-to-date. That is a very odd scenario. Well done for fixing it.
HOW DID YOU CREATE THE Source CODE FOR THE TWO TABLES? DO YOU HAVE A FUNCTION YOU COULD SHARE? PLEASE???!!!
Two comments. The start date must always be January 1 of the first year and the end date must always be December 31 of the last year. To get this I chose to use:
StartDate = #date(Date.Year(List.Min(Transactions[Date])),1,1),
EndDate = #date(Date.Year(List.Max(Transactions[Date])),12,31),
Also, after the Fill Up operation, it's a LOT easier to filter null in the Year column than the Date column, especially for a calendar for more than one year.
Still a valuable video!
The Tables were created with Enter Data. But could come from anywhere.
You can find them in the Example file - which is available in our free Insiders program.
Excelente video… 👍🏽
Thanks 😁
We use 4,4 ,5 - your solution is good for a single year, not so good with a large date range - Had hoped it was going to use equivalent of Excels weeknum and type 21 - that's been stumping me a bit
Why won't it work for a large date range? You could easily create it for the next 20 years. How far do you need to go out?
@@ExcelOffTheGrid apologies Mark - I was thinking Leap years etc would throw it out and mean you would have to add the period end/start dates fairly manually, but i do now think it would work - thank you - sorry for jumping to wrong conclusion
Get an error EVERYTIME i try to enter this. I am using Office 365 Power Query. I have written it 4 times EXACTLY as you show and everytime I hit the DONE button I get this error "An error occurred in the ‘’ query. Expression.Error: The name 'Startdate' wasn't recognized. Make sure it's spelled correctly."
Any idea, I give up on learninig if I get unexplained arrors all the time.
Power Query is case sensitive. So your variable is probably called StartDate, but then you’ve entered Startdate when you have tried to use it.
That is my guess.
@@ExcelOffTheGrid FOUND IT. I didn't know it was case sensative. Once I fixed that it works. I have a lot to learn about PQ. But I plan on learning it. I can see where its power can be a GREAT addition to Excel. Thanks for the help.