In this short article, I will share with you a simple technique to create a Dates or Calendar table template in Tabular Editor and add to any Power BI file using a macro.
When you start building a new Power BI project or as you build a sample model for testing or experimenting purposes, a Dates table becomes an important part of your model. By using Tabular Editor, you can create a Dates table script and store it as a macro, so you can apply it to any model to add a dates table. It eliminates the need to create the table from scratch or copy it from somewhere else.
If you are familiar with adding a Macro in Tabular Editor, use the following script and proceed otherwise follow the steps shown in the images below.
C# Script - Dates Table
var dax = @"ADDCOLUMNS( CALENDAR(""01/01/2021"",""31/12/2021""), ""Month No"" , MONTH([Date]), ""Month Name"" , FORMAT( [Date] , ""Mmmm"" ), ""Year"" , YEAR([Date]), ""Month Year No"" , (YEAR([Date]) * 100) + MONTH ([Date]), ""Month Year"" , FORMAT( [Date] , ""Mmm yyyy""), ""Quarter"" , QUARTER([Date]), ""Year Qtr"" , FORMAT( [Date] , ""YYYY \QQ""), ""Week Day"" , WEEKDAY([Date],2), ""Week"" , FORMAT( [Date] , ""Dddd"" ) )"; Model.AddCalculatedTable("Dates", dax);
Steps to create a Dates Table and save it as a Macro in Tabular Editor
If you haven’t installed Tabular Editor in your machine, you can download and install it from this LINK, there are two versions , The version 3 is a paid application and the the version 2 is free. You can perform the following task with any version.
Once you have added the Tabular Editor, you can restart Power BI and find the Tabular Editor under he External Tools tab. Follow these steps to create the dates table and the macro:
Adding a Dates Table to a Power BI File
You have now added a Macro in Tabular Editor which can be run on any Power BI file.
To add a Dates table to your file, Click on the External Tools > Tabular Editor and follow the steps shown below.
Hope this article was helpful to you? Please leave your comments, suggestions or questions below.
Fowmy Abdulmuttalib is a Microsoft-certified business intelligence professional with extensive experience in finance, accounting and data analytics. Currently, he is employed by McDonald’s Saudi Arabia as the Head of ERP and Business Intelligence. He is interested in everything data-related, he likes to share his passion and knowledge with the data community.