Have you ever imagined how nice it would be if you did not have to create your Power BI calendars in DAX or in Power Query? Here is the solution. FREE Always updated calendars in Google Sheets for everybody.
No-DAX Calendar for Power BI
So what’s the trick? I’ve created several public calendars sitting in Google Sheets which can be exported as live URLs and then used in your Power BI projects. The calendars only differ by their time zone settings – currently CEST, GMT, EST, PST time zones are available. The calendar itself looks like this:
These are all the available columns: Date Date as Text, Date as Number, Day of Week, Day Full Name, Week Of, Week Num, Year-Week Num, Month Number, Month Name, Half, Quarter, Year-Month, Year, Year-Quarter, Last X Days, Last X Weeks, Last X Weeks Rolling, Last X Months, Last X Quarters, Last X Years, Week Rolling (Today-2D), YoY Comp, Last Refreshed, Last X Months | YoY, Week Of | FRI, Last X Weeks | FRI, Week Num | FRI, Day of Week | FRI, Week Of | SUN, Last X Weeks | SUN, Week Num | SUN, Day of Week | SUN
Few comments about the column names:
- Last X Columns – they show the last Xth element relative to “today”. So Last X Days let’s you to filter for “Yesterday” or for “Yesterday-01”
- Columns ending with | FRI and | SUN – those columns take Friday and Sunday as start of the week, otherwise the week starts always on Monday
- All the columns are automatically generated based on formulas in Google Sheets
- Last Refreshed column – shows time of the last refresh of the Google Sheet
I commit not to change column names AND not to remove columns in future.
A Few More Extras
I’ve also created 2 extra sheets which tell you number of days in months and quarters.
How to Get Access to These Calendars?
Get access by proceeding through the download button:
Once you select your link, you need to use it as data source in Power BI:
Right after the import, it’s probably a good idea to remove the automatic “Changed Type” step in Power Query editor and then set data types manually – I normally leave everything as text except Week of and Date as Number.
And that’s it – your calendar is now created and you are free to use it in your data model 🙂
As you can see this is not rocket since but it can save you some DAX or M-language copying and pasting in future 🙂
If you’ve noticed any problems, please leave me a comment in the section below.