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
@anillpatel3 ай бұрын
This has to be one of the best video presentations on Calendar Dates. Thanks Mark.
@ExcelOffTheGrid3 ай бұрын
Thank Anil 😁
@rpopecpa2 ай бұрын
The best video presentation on Calendar Dates!! Thanks.
@ExcelOffTheGridАй бұрын
Wow, thanks. I’m glad it was helpful!
@ashleysummerell758312 күн бұрын
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
@peterscobbie32303 ай бұрын
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 🙂
@ExcelOffTheGrid3 ай бұрын
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. 👍
@mathyustrod67764 ай бұрын
Fantastic video as always! Your PQ vids have been a lifesaver for my occasional large data tasks, from a crawl to swift delivery. ⚡️👍🏾
@ExcelOffTheGrid4 ай бұрын
That's great news. I'm glad to help.
@JohnWickXL2 ай бұрын
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.
@ExcelOffTheGrid2 ай бұрын
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.
@JohnWickXL2 ай бұрын
@@ExcelOffTheGrid Thanks Mark. That makes complete sense to me, and is what I feared! I'll continue with my workaround method instead.
@ivanbork41754 ай бұрын
You are good, both in choosing the topic and make them easy to implement Thank you
@ExcelOffTheGrid4 ай бұрын
Thank you, that's very kind of you to say.
@vinothkumarmanoharan23174 ай бұрын
Awesome Level of teach and special thanks
@ExcelOffTheGrid4 ай бұрын
Thanks - I'm glad you like it. 😁
@jackgrayson95674 ай бұрын
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?
@ExcelOffTheGrid4 ай бұрын
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.
@jackgrayson95674 ай бұрын
@@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.
@ankursharma61574 ай бұрын
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!
@ankursharma61574 ай бұрын
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!
@ExcelOffTheGrid4 ай бұрын
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.
@ankursharma61574 ай бұрын
O.K. Thank You for Your Response. Appreciate!
@tibibara2 ай бұрын
Thanks Mark!
@Back1Ply4 ай бұрын
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
@ExcelOffTheGrid4 ай бұрын
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.
@teersanbar4 ай бұрын
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?
@ExcelOffTheGrid4 ай бұрын
It sounds like you need to use CALCULATE, using the dates and blank to change the filter context.
@teersanbar4 ай бұрын
@@ExcelOffTheGrid thank you. I’ll give that a shot.
@alanmonaghan91944 ай бұрын
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
@ExcelOffTheGrid4 ай бұрын
Is it in on workbook or all workbooks?
@alanmonaghan91944 ай бұрын
@@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
@alanmonaghan91944 ай бұрын
@@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
@ExcelOffTheGrid4 ай бұрын
Thanks for keeping me up-to-date. That is a very odd scenario. Well done for fixing it.
@vishnuvardanatmakuri4 ай бұрын
fantastic one! You could also have worked on Quarters for by CY and FY... that would of great use!!
@ExcelOffTheGrid4 ай бұрын
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.
@jerrydellasala76434 ай бұрын
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!
@ExcelOffTheGrid4 ай бұрын
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.
@GJD9874 ай бұрын
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
@ExcelOffTheGrid4 ай бұрын
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?
@GJD9873 ай бұрын
@@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
@shubhabratadey4 ай бұрын
Very informative video
@ExcelOffTheGrid3 ай бұрын
Glad you liked it 😁
@gonzuic4 ай бұрын
Excelente video… 👍🏽
@ExcelOffTheGrid4 ай бұрын
Thanks 😁
@gregory65294 ай бұрын
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.
@ExcelOffTheGrid4 ай бұрын
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.
@gregory65294 ай бұрын
@@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.