If you are a Power BI user, you have created more than one calendar.
There are a lot of calendars you can create:
- A standard calendar
- A 445, 454, 544 and 13 weeks calendar
And of course, there are many ways to create them:
- Calendar in Power Query
- Calendar in DAX/ Powerpivot
There is no right or wrong here, your data will determine which calendar to use and how.
We have in Curbal’s YouTube channel explain all this into detail, so this is a summary of what it already has been covered there.
Sample files for all calendars are available on the comments box of each video!
So, lets begin!
Create a standard calendar in Power Query:
Create a calendar that self-generates and is self-configurable:
Create a 445,454,544 and 13 days calendar:
Create a calendar with DAX CALENDAR and CALENDARAUTO:
Create a calendar using the equivalent of CALENDAR in PowerQuery:
Download the sample files:
To download the file go to our download center *HERE*.
Hi,
I have a calendar table that goes up to December 31st, 2018. How can I extend it please? Here is how it was created:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“(lots of gibberish)”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Calendar = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Calendar”, type date}})
in
#”Changed Type”
And the last few lines of data in the table:
2018-12-25
2018-12-26
2018-12-27
2018-12-28
2018-12-29
2018-12-30
2018-12-31
It looks hard coded, use mine instead.
/Ruth
Cannot download the PBIX file – looks a common issue here.
Rgds..Aziz
Hi,
I have created a download center where you can get all my pbi files in one place. Here it is:
https://curbal.com/donwload-center
/Ruth
I tried this for a 2019 calendar starting Feb 3 and get error on load ‘increment’ argument is out of range. Does your template go out to 2019? are you going to update it?
Better still do you have a template to create an Excel spreadsheet for 2019 4-5-4 calendar? I want to get this into a SharePoint calendar or list and Excel makes it easy, albeit an extra step if I could go straight to SharePoint. Thanks
What is the best recommendation to use 445 or 544 or 454 calendar for developing data model
That is for the business to decide. Ask them which one they need!
/Ruth
This is awesome! I tried to change the parameters to begin in 2012, Month 1 (January), Date 1 (1st), and day 0 (Sunday). The underline data that gets generated is a bit messed up? Am I missing something?
Hi,
It my have to do with your regional settings. Check every step in power query to see what makes the query break.
/Ruth
gofile.me website is not accessible from my work. Can you perhaps put it somewhere else and let me know and I will try it out. I work for one of those big scared companies, but I suspect other people can’t get to gofile.me as well.
Thanks for great tutorial anyway… Also, you might become famous and explain 445 calendar on wikipedia.org page which doesn’t explain 445 at all!
https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar
HI,
I will try to get the files somewhere else. 🙂
/Ruth
Hi Ruth,
I have a scenario that our 2017 FY calendar has a 455 pattern for the 1st Quarter and 445 pattern for 2nd, 3rd, and 4th Quarter.
and the same thing with our 2018 FY calendar.
need your help on how to deal with this matter. let me know if it’s OK with you that I’ll send a sample of our FY calendars.
More power.
I hope you could spare some time and hope to hear from you soon inspite of your busy schedule
Thank you in advance.
*NOTE: perhaps you could add a upload button/link on your Blogs.
regards,
Norman
could you do a video on how to split a column that has 40.000gals on one row and 6.256oz on another in powerbi
Hi Bill,
Can you provide more details as what you are trying to achieve?
/Ruth
No matter what parameters i entered I always got the error message saying “The ‘increment’ argument is out of range.
” What’s wrong with it? Please comment.
Hi Julian,
I have sample files for you to download on all the calendars. The download file is in the description box of the videos. Have you tried them to see how they differ from yours?
/Ruth