[Power BI] 💡 Creating a Calendar Table in the Power Query Editor 📅

Having a dedicated Date or Calendar table in your data model is crucial for most of our reporting in Power BI, but many people struggle with understanding how they can create a date table or even with where they can get a date table from.
In this video, Jonothan walks you through 4 easy steps of how to leverage M and create a date table in the Power Query Editor. Don’t worry, he doesn’t just stop there… he also explains some of the different methods we can use to add additional columns, like month name, month number, and quarter.
👍If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Jonathon30" at check out when purchasing our On-Demand Learning classes to get extra 30% off - pragmaticworks.com/pricing/
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning - Start With The FREE Community Plan: tinyurl.com/2ujujsb5
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/

Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️CZcams: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
Email: [e-mail]
~-~~-~~~-~~-~
Please watch: "(275) Free Pragmatic Works Power Apps Component Library "
• Free Power Apps Compon...
~-~~-~~~-~~-~

[@AliHoolash]

Great tutorial! Thanks for sharing.

[@mLenkaxx]

Hi, I was trying to mimic what you were doing and got stuck in minute 6:17. I typed exactly the same formula but it shows me the "Token RightParen expected" error. When I click "show error" it highlights 2 letters in a word Number of the second formula "be". Do you have any idea how to fix this?

[@tilayealigaz9595]

I like your way of teaching!
Thank you for that

[@FRANKWHITE1996]

Thanks for sharing! 👍

[@pottewarmadhav9474]

Good one and best explanantion and clearly easily understand the subject .Thanks a lot

[@PragmaticWorks]

Glad you enjoyed!

[@boazemuna6589]

Thank you very much, this video really get me into build a fine calendar. easy and fast. Tnx again

[@PragmaticWorks]

Thanks for watching!

[@janeguo4103]

Thanks for sharing! super helpful

[@mattisandkvist783]

Can you show how to add column with weeknumbering in the same formatting style as in Oracle with TO_CHAR('IW') (according to ISO-standard).

[@hendrag1174]

Thank you very much for this great video..

[@yuriyupo1049]

Great one! Thanks. How can it be integrated with working days and scheduled holidays if, for example, I want to count only working days between dates? Thanks in advance.

[@Jonathon_Silva]

Hi Yuri, there are a few ways we can count the working days between dates - either using M in the Power Query Editor or with DAX and both ways require a bit of work to accomplish. You can email me at [e-mail] directly and I can try to get you in the right direction from there if you would like.

[@aryastark5886]

Hello 👋 maybe you could help me about other topic but related to date as well.
Problem : For my End date, I want to get the end of the month (date format) of my previous month as of today end of month local now values. Would this be possible?
Example;
Today EOM: Feb 28, 2022
Goal
Prev EOM: Jan 31, 2022
Hope you can help me resolve this one. Many thanks 😊

[@zulajenkins658]

Thanks. What are differences between creating date table using M code and DAX?

[@Jonathon_Silva]

Hi Zula, the major difference between building a calendar or data table using M and DAX is that the M language in the Power Query Editor provides better overall compression for the data model. Typically, we suggest doing as much as possible in the Power Query Editor when building the data model, then you can add to that model, using DAX, where you need to do any additional analysis calculations.

[@geletawadank5706]

you are the best

[@PragmaticWorks]

Thanks for watching!

[@StopWhining491]

Assume final step would be to add the table to the data model?

[@diala_the_bookkeeper]

I read this line in the dax manual by Microsoft and I don't get it. they practically mention it for every dax function. "This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules."

[@Reventrant]

DirectQuery is fairly limited on the number of transformations you can do with the data.

[@sharonhowell159]

Your end date is fixed; how do we get the end date to grow? What if I wanted the end date to be 2 weeks after current date?

[@Jonathon_Silva]

For the end date try the following: Date.AddYears(Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())),1) to have a dynamic end period in the calendar. In this case it takes the end date to be one year from the current date.

[@xMastJedi]

there is no month 14.....

[@gracy7063]

You’re handsome:)

[@SANDEEPGUPTA-io3ms]

Very helpful thanks for sharing I always learn power bi from @pragmatic works❤❤