Miguel Escobar Published August 29, 2017 
Power Query and Power Pivot are AMAZING tools, but if you want to create a Calendar Table with either one of them, you’ll either have to learn how to code in M or DAX, or copy/paste a code that you probably found on the web like this one.
The problem with that is that every time that you need to create a Calendar Table you need to go through that whole process, and going through either M or DAX code could be intimidating to even a seasoned player. There’s simply no easy or user-friendly interface or portability, but you STILL need a calendar table if you’re trying to use Power BI’s Time Intelligence functions.
What if there was a single button that could create that Calendar Table for you?
Imagine a button that sits right in the ‘Get Data’ window and once you click it, it’ll ask your for a few parameters like start date of your calendar table and, once you hit OK, you’ll get EXACTLY the calendar table that you were looking for?
Well, this is now possible! and it’s all thanks to the Power BI Custom Connectors.
Not only can you use the Custom Connectors to connect to new data sources, but you can also implement solutions like this one that make things easier for the end-user.
No more copy/pasting. No more long lines of code in either DAX or M.
Just click the button, provide the parameters and you get exactly the table that you want and in your own language!
Watch the Calendar Creator buttons in Action
(Fiscal Calendar Creator Demo)
(Week based Calendar Creator Demo for 4-4-5, 4-5-4, 5-4-4)
Give it a try. Is completely FREE!
Currently the solution can create 2 types of Custom Calendar Tables:
- Calendar Table with Fiscal Columns
- Calendar Table based on the 4-4-5, 4-5-4, and 5-4-4 patterns
Note: the option to add a ISO 8601 Calendar Table will be added within the next few weeks
Here’s a list of steps so you can give this custom connector a try:
-
Create a [My Documents]\Microsoft Power BI Desktop\Custom Connectors directory
-
Download the Calendar Creator extension from here and save it in the directory created in step #1
-
Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)
-
Restart Power BI Desktop
-
Find the Calendar Creator buttons in the ‘Get Data’ window
Note: While custom connectors are still in a preview phase, you can’t refresh them on the Power BI Service, but that will change sooner than you think!
Contribute and provide feedback
This project is something that I’ve been working on by myself, but I need your help! Please give this connector a try and provide any suggestions, criticism or report bugs (if there are any!) either in the comment section of this blog post or on Github.
Custom Connectors will become discoverable and available through the Office Store, so you can imagine that one day you won’t even have to download the custom connector from a link, but rather directly from within the Power BI Desktop just like it happens today with Custom Visuals.
Caveat: An Authentication window?
You might have noticed from the videos above that when I try creating a calendar from the custom connector, it requires me to go through an Authentication window like this one:
This is something that I’ve brought up to the Microsoft team and it requires your support so they can get rid of it. Since we are not connecting to any data source, we shouldn’t be prompted for any sort of authentication and in order to get rid of this, we need to go ahead and upvote this idea.
Hi Miguel,
Great work. Does the Fiscal year default to July > June ? is that flexible at all?
Thanks
Wyn
Hey Wyn!
Thanks for the kind words. Actually, there is no default mode.
The 2nd parameter of the Fiscal Calendar requires a YearEnd example, so you need to select from the date picker a date as an example. I believe I used 30-Jun of a random year (it can be any year. What matters is the actual day and month).
So, for example, you can input the parameters like this:
And it’ll automatically calculate a fiscal calendar where your year ends on March 31st. Changing the 2nd parameter defines in what day/month your fiscal year ends.
Ah 🙂 briliiant!
Well, this is now possible! and it’s all thanks to the Power BI Custom Connectors.
Can you make the end date dynamic? My current approach for creating a date table is using today() function to define the last day of my calendar table because it’s pointless to include days that don’t have transactions in your fact table. Also can you add Sydney/Australia in the locale settings
Hey!
You absolutely can make the end date dynamic. You can replace the 2nd parameter with any variable or function that you want as shown in the next picture:
Btw, do you have by any chance the culture/locale code for Sydney/Australia? is it en-au?
I chose not to put the full table of locale/culture:
https://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
to simplify the UX, but I wouldn’t mind adding just one for Australia/sydney
Cool, thanks for pointing this out. Yes, the locale for sydney/australia is en-au
awesome! I’ll add this during the weekend. Is it currently giving you the correct values? have you found any bugs?
This looks great! The Fiscal.CalendarCreator has the additional prompt for the week format — this doesn’t match your demo video. Am I missing something?
Also, what practice would you recommend to extend the calendar each month, automatically ideally?
Michael! Thank you! Great feedback. I made that change by mistake and I just corrected it 🙂
You should be able to see a new parameter for both functions shown in the videos of this blog post. The new parameter is called End Date and, as the name suggests, you can define the last date of your dates table with it.
The best way right now, taking in consideration the authentication limitation, is to create a table with a really futuristic end date, like a date in the year 2030. Then, you’d create a date parameter inside Power BI Desktop and use that parameter to filter the date table dynamically. That date parameter can have a dynamic value, so every time that you refresh the report it recalculates.
Let me know your thoughts!
Your correction fixed it. Thank you for the quick response!
I like your suggestion about setting the date far in the future and adding a dynamic parameter.
Your response to Bo Gao stated you wanted to keep the UX simple, but I wouldn’t complain if you ever choose to add Canada English (en-ca), It’s not much of an issue though since I can change the format of the DateKey.
hey, thank you for providing feedback!
just for providing feedback, I’ll add that culture/language to the UI 🙂
Quick question, is there a notable difference between English (US) and English (Canada)? I only use the culture/locale for the Text columns as the other columns don’t really have an option to set up the locale/culture.
Commonly, the culture/locale is used to Transform a value into a specific data type using the logic/format of a culture/locale, but since I’m not doing any transformation I’m curious to know if there’s a difference in the text columns even in the same language.
It’s a good question, Miguel. There is no difference in text (month names or day names) between US and English (Canada). Depending on whom you ask, there could be a difference in date format (e.g. month, day, year versus day, month, year), but from what I’ve seen different companies may choose different formats.
Your explanation tells me there is not much need for a separate en-ca.
And just in case your curiosity goes further, there are some differences in spelling between US and English (Canada), for example: US color, center; English (Canada) colour, centre. Canada has traditionally followed UK spelling.
Hi
Any idea when the ISO 8601 will be ready. I have a lot of week based data and I would really love that.
Thanx
Hey!
Hopefully next week – i’ve been pretty busy lately 🙂
Best!
I used this awesome tool to create a 4-5-4 fiscal calendar starting on 1/1/17 and ending 12/31/17. I have this issue: the data table is listing 12/31/2017 as Q1 and not part of Q4. Any idea how to correct this?
Hey Miles!
Could you please share your query with me? Just head over to the View tab, click on the Advanced Editor, copy everything that you see in the Advanced Editor window and paste it here 🙂
Thx for trying out this connector!
let
Source = Week.CalendarCreator(#date(2017, 1, 1), #date(2017, 12, 31), “4-4-5”, “English (United States)”),
#”Sorted Rows” = Table.Sort(Source,{{“DateKey”, Order.Descending}}),
#”Appended Query” = Table.Combine({#”Sorted Rows”, dimDate2016, dimDate2015}),
#”Renamed Columns” = Table.RenameColumns(#”Appended Query”,{{“Year”, “Fiscal Year”}}),
#”Sorted Rows1″ = Table.Sort(#”Renamed Columns”,{{“DateKey”, Order.Descending}})
in
#”Sorted Rows1″
You can ignore after the Sorted Rows…I’m just adding the 2015 and 2016 fiscal calendar since every year the week format changes
Hey Miles,
The function that you used currently works with only a 52 week year. it basically creates symmetrical years where every year will have exactly the same length of days (364 days) and this is why the 31st is part of the next year. There is also another option from the GET Data window to select a Fiscal 52-53 weeks calendar creator, but I believe that your requirements is a custom one.
What I’d do is basically extract that record (basically filter out that 31st of december record) and then add a new record to your original table that holds the correct values for that specific date.
I’ll try that. Thanks for the quick response.
General feedback for the tool: it would be nice for it to also output month names. I’m finding that many people see month numbers and the fiscal month label in the x axis and its not completely intuitive that they are looking at months. Seems intuitive to me…but unfortunately its not about how i see things :/
thanks for the feedback! I truly didn’t want to add that many columns as it wouldn’t look inviting to look at all of the data. That’s the main reason behind why some columns are not there, and because, most of them, could be created with just PQs UI.
Hi Miguel,
Any update on when this will be refreshable from within the Service? Would be great to use but so far can’t due to this limitation, all my reports get published to the service.
I have published a report with the fiscal calendar and unchecked the ‘Include in Report Refresh’ option box, but my refreshes in the service keep failing with an supported data source error. Do you know of any way to bypass this error? I don’t need the calendar to update, just the rest of the tables!
Regards and keep up the good work.
Matt
Yes, this needs to go. I am sorry to state, no offense to developers, that PowerBI is becoming limited to developer knowledge. Miquel is one of the few going outside the box to really address the connection issue.
B
is there a way to adjust what year the 53 week hits?
Hey! Would you mind telling me more about what years should have the 53 weeks and on what week? Maybe I could give you some suggestions on what parameters to input
We have a July year beginning and are wanting to put the 53rd week into 2021. I have a 4-5-4 starting june 30th 2019. i am thinking about sticking the extra week into either week 5 of july, week 5 of september (week 14 of 2021), or at the end of the year june 2022
Cool idea project, hoping you will come back.