{"id":1234,"date":"2020-06-14T20:16:22","date_gmt":"2020-06-14T20:16:22","guid":{"rendered":"http:\/\/bi.unija.com\/en\/?p=1234"},"modified":"2020-08-17T13:50:46","modified_gmt":"2020-08-17T13:50:46","slug":"dynamic-calendar-in-power-query","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/","title":{"rendered":"Create a Dynamic Calendar in Power BI"},"content":{"rendered":"

In this article we’ll cover how to create a dynamic calendar in Power BI, using the Power Query Editor.<\/em><\/p>\n

Calendar table is the most important table in Power BI model. We use it to connect to big fact tables and filter or aggregate our data. It enables us the use of time intelligence functions that only work on continuous time range like TOTALYTD()<\/strong> TOTALMTD()<\/strong>,\u00a0DATESBETWEEN()<\/strong>, etc. You can read more on calendar table in the article Calendar in Power BI<\/a>.<\/p>\n

Power BI will generate calendar table for each date field in our data. If we have three date fields in our tables, for example Date, OrderDate and PaymentDate, we would get three separate hidden calendar tables, each for one field. These would slow down our model. Good practice is to turn automatic generation of calendar tables off and create a calendar table ourselves. We can turn off this setting by following File\u00a0> Options\u00a0and\u00a0settings\u00a0> Options\u00a0> Data\u00a0Load\u00a0> Global\u00a0> Auto\u00a0date\/time\u00a0for\u00a0new\u00a0files<\/strong>.<\/p>\n

How To Create a Dynamic Calendar Table in Power Query<\/h1>\n

Let’s create a dynamic calendar table through an example. We will open the following business data in Power Query.<\/p>\n

<\/p>\n

Fact table with Date field.<\/em><\/p>\n

Appending Queries<\/h2>\n

We have date fields in queries Actual and Plan. We reference both queries by right-clicking and selecting Reference<\/strong>. We name the new references Calendar and hCalendar. We then select Date field in table Calendar, right-click and select Remove Other Columns<\/strong>. We do the same for hCalendar. We now only have Date field in both tables. We merge tables: we first select Calendar and then Append Queries<\/strong>. We select to append table hCalendar.<\/p>\n

<\/p>\n

We append hCalendar to Calendar.<\/em><\/p>\n

We now have all the dates from our model in one date field of table Calendar.<\/p>\n

Finding Start and End Date<\/h2>\n

From all the dates we really need just two: the earliest and the latest. From these two we’ll make our calendar table that will consist of the first day of earliest year and end with the last date of latest year. Example: if earliest and latest date in our data model are 7\/3\/2010 and 10\/15\/2019, we want a calendar table of all the dates between 1\/1\/2010 and 12\/31\/2019.<\/p>\n

To find the two dates we need, we start by calculating the first day of the year in our new Calendar table: we right-click and select Transform\u00a0><\/strong>\u00a0Year\u00a0><\/strong>\u00a0StartOfYear<\/strong>. This transforms all the dates to the first day of each year. We then remove duplicated with right-click, Remove Duplicates<\/strong>. This step isn’t necessary, but it speeds up further transformations. We’re left with unique dates. We duplicate the column by right-clicking and selecting Duplicate Column<\/strong>. In new column we right-click and select Transform\u00a0><\/strong>\u00a0Year\u00a0><\/strong>\u00a0EndOfYear<\/strong>. We now have columns with beginnings and ends of years.<\/p>\n

<\/p>\n

Start and end dates of all the dates in our data model.<\/em><\/p>\n

We change data type of both fileds to numbers. Dates in Power BI are actually numbers that represent days passed since 1\/1\/1900. For example, if we were to convert number 5 to date type, we would get 1\/5\/1900.<\/p>\n

<\/p>\n

We change dates to numbers and sort them: ascending in first column and descending in second.<\/em><\/p>\n

As we see, earliest and latest date are actually just the smallest and largest number. We find the smallest number in first column: we select Transform\u00a0><\/strong>\u00a0Statistics\u00a0><\/strong>\u00a0Minimum\u00a0<\/strong>(see image above). We get a step that returns the smallest number in the model. In the Applied Steps<\/strong> window, we rename this step to MinDate. We then find the latest date: we duplicate the MinDate step and rename this new step to MaxDate. We modify the formula in the step from Min to Max.<\/p>\n

<\/p>\n

We duplicate the MinDate step, rename it to MaxDate and modify formula to Max.<\/em><\/p>\n

We now have start and end date in two steps, which we’ll use in creating our calendar table.<\/p>\n

Creating a list<\/h2>\n

We’ll create a list of dates using start and end date. We click the formula sign fx<\/strong> and select Insert Step<\/strong>. We confirm the option with Insert<\/strong>. We then enter = {MinDate..MaxDate} <\/strong>in formula bar.<\/p>\n

<\/p>\n

Using formula in the image above, we create a range of dates between StartDate and EndDate.<\/em><\/p>\n

Formula generates a list of dates between the dates. We convert the list to table by right-clicking and selecting To Table<\/strong>. We then convert data type to date. We now have a table of all the dates we need in our data model.<\/p>\n

M function<\/h1>\n

All the transformations we used in this tutorial were stored in Power Query in the form of formulas. We can see all the steps in the Applied Steps <\/strong>window. Formulas are written in a language called M. We can see the formulas in the formula bar.<\/p>\n

We can join the steps to a single function that we use every time we create a new dynamic calendar. We’ll explain what the function looks like and how to use it in our next article, Dynamic Calendar in Power Query using an M function<\/a>.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

In this article we’ll cover how to create a dynamic calendar in Power BI, using the Power Query Editor. Calendar table is the most important table in Power BI model. We use it to connect to big fact tables and<\/p>\n","protected":false},"author":5,"featured_media":1216,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[6,9],"class_list":["post-1234","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-calendar","tag-power-query"],"acf":[],"yoast_head":"\nCreate a Dynamic Calendar in Power BI - Unija Smart<\/title>\n<meta name=\"description\" content=\"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create a Dynamic Calendar in Power BI - Unija Smart\" \/>\n<meta property=\"og:description\" content=\"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-14T20:16:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-17T13:50:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/\",\"name\":\"Create a Dynamic Calendar in Power BI - Unija Smart\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg\",\"datePublished\":\"2020-06-14T20:16:22+00:00\",\"dateModified\":\"2020-08-17T13:50:46+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"description\":\"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.\",\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create a Dynamic Calendar in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Create a Dynamic Calendar in Power BI - Unija Smart","description":"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/","og_locale":"en_US","og_type":"article","og_title":"Create a Dynamic Calendar in Power BI - Unija Smart","og_description":"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.","og_url":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/","og_site_name":"Bi Unija","article_published_time":"2020-06-14T20:16:22+00:00","article_modified_time":"2020-08-17T13:50:46+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg","type":"image\/jpeg"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/","url":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/","name":"Create a Dynamic Calendar in Power BI - Unija Smart","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg","datePublished":"2020-06-14T20:16:22+00:00","dateModified":"2020-08-17T13:50:46+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"description":"Calendar table is the most important table in Power BI model. Let's look at how to create a dynamic calendar in Power BI step by step.","breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG4.jpg","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Create a Dynamic Calendar in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1234"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1234"}],"version-history":[{"count":6,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1234\/revisions"}],"predecessor-version":[{"id":1600,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1234\/revisions\/1600"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1216"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}