{"id":1249,"date":"2020-06-14T20:19:19","date_gmt":"2020-06-14T20:19:19","guid":{"rendered":"http:\/\/bi.unija.com\/en\/?p=1249"},"modified":"2020-08-17T13:51:29","modified_gmt":"2020-08-17T13:51:29","slug":"dynamic-calendar-in-power-bi-with-dax-function-calendarauto","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/","title":{"rendered":"Dynamic Calendar in Power BI with CALENDARAUTO()"},"content":{"rendered":"

In this article we\u2019ll create a dynamic calendar in Power BI using CALENDARAUTO() function.<\/em><\/p>\n

Calendar is the most important table in Power BI data model. We have seen how calendar table looks in our Calendar in Power BI article<\/a>. We have also created a dynamic calendar in Dynamic Calendar in Power BI Using an M Function<\/a>. Today we\u2019ll create a dynamic calendar using a DAX function.<\/p>\n

CALENDARAUTO() function<\/h2>\n

Formulas in Power BI are written in DAX language. Syntax is very similar to Excel\u2019s formulas. Function for creating a calendar is CALENDARAUTO(). <\/strong>Function finds the earliest and latest date in our data and generates a list of all the dates between the beginning of the earliest and the end of the latest year. For example, if those dates are April 3, 2000 and November 20, 2019, CALENDARAUTO() <\/strong>will generate a list of all the dates between January 1, 2000 and December 31, 2019.<\/p>\n

Data table.<\/em><\/p>\n

We create a calendar by clicking New Table<\/strong> and typing Calendar = CALENDARAUTO() <\/strong>in formula field. We confirm the formula with Enter. We get a new table Calendar, that contains all the dates in our data model.<\/p>\n

<\/p>\n

Creating a simple calendar with CALENDARAUTO().<\/em><\/p>\n

We usually want to know date attributes like day, month, quarter and year. Attributes allow us to filter our data. When we want to know our expenses in the year 2019, Power BI uses a calendar table to find all the dates in year 2019 and returns an aggregated result.<\/p>\n

We describe each attribute in its own column. For month attribute we click New Column<\/strong> and type Month = MONTH(Calendar[Date]) <\/strong>to formula bar. We do the same for day, week, quarter, and year. We use following functions:<\/p>\n

Quarter = QUARTER(Calendar[Date])<\/strong>,<\/p>\n

WeekdayNo = WEEKDAY(Calendar[Date])<\/strong>,<\/p>\n

WeekNo = WEEKNUM(Calendar[Date])<\/strong>.<\/p>\n

Custom DAX function<\/h2>\n

We can store all the manual steps into a function and use it every time we need to create a calendar table. The function creates a calendar table of all the dates in the data and all of its attributes. The function is fairly simple:<\/p>\n\n\n\n
Calendar = ADDCOLUMNS ( CALENDARAUTO ();<\/p>\n

“Year”; FORMAT([Date]; “yyyy”);<\/p>\n

“MonthNo”; MONTH([Date]);<\/p>\n

“Month”; FORMAT([Date];”MMM”);<\/p>\n

“Quarter”; FORMAT([Date];”\\QQ”);<\/p>\n

“YearMonth”; FORMAT([Date];”YYYY-MM”);<\/p>\n

“WeekdayNo”; WEEKDAY([Date];2); \/\/1-Sun..Sat; 2-Mon..Sat<\/p>\n

“Weekday”; FORMAT([Date];”ddd”);<\/p>\n

“WeekNo”; WEEKNUM([Date]; 2);<\/p>\n

“Week”; “W” & WEEKNUM([Date]; 2) )<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Function that creates a calendar table in Power BI.<\/p>\n

We use it the same as we used function CALENDARAUTO()<\/strong>: we click New Table<\/strong> and paste the code into formula bar. We get a calendar table:<\/p>\n

<\/p>\n

Final look of the calendar table.<\/em><\/p>\n

Problem with CALENDARAUTO(): date and time Power BI<\/h2>\n

Using CALENDARAUTO() is simple and fast, but it has some disadvantages. If we have a time field in our data, it will be treated as a date January 0, 1900. Time in Power BI is a decimal number, representing a share or a part of the day. Similarly, day is a number, representing number of days passed since January 1, 1900. For example, number 0.5 represents 12 pm on January 0, 1900. If we convert a decimal number between 0 and 1 to a date, we\u2019ll always get January 0, 1900. CALENDARAUTO() <\/strong>will read this as the earliest date (it\u2019s unlikely we would have any even earlier dates in our data) and set 1899 as the earliest year in our data (since January 0, 1900 doesn\u2019t exist). This will create a huge calendar table, that will slow our model down.<\/p>\n

<\/p>\n

Power BI treats numbers as dates and vice versa. In upper example, we have the same numbers in all three columns, formatted as number, date and time.<\/p>\n

Similar problem arises if we have birth dates in our data. The function will again read it as the earliest date entry and create a big calendar table that will slow our model down. Better way to create a calendar table is shown in Dynamic Calendar in Power Query using an M function<\/a>.<\/p>\n

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

In this article we\u2019ll create a dynamic calendar in Power BI using CALENDARAUTO() function. Calendar is the most important table in Power BI data model. We have seen how calendar table looks in our Calendar in Power BI article. We<\/p>\n","protected":false},"author":5,"featured_media":1204,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[6,5,8],"class_list":["post-1249","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-calendar","tag-dax","tag-power-bi"],"acf":[],"yoast_head":"\nDynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart<\/title>\n<meta name=\"description\" content=\"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.\" \/>\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-bi-with-dax-function-calendarauto\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart\" \/>\n<meta property=\"og:description\" content=\"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-14T20:19:19+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-17T13:51:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.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=\"3 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-bi-with-dax-function-calendarauto\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/\",\"name\":\"Dynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg\",\"datePublished\":\"2020-06-14T20:19:19+00:00\",\"dateModified\":\"2020-08-17T13:51:29+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"description\":\"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.\",\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic Calendar in Power BI with CALENDARAUTO()\"}]},{\"@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":"Dynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart","description":"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.","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-bi-with-dax-function-calendarauto\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart","og_description":"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.","og_url":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/","og_site_name":"Bi Unija","article_published_time":"2020-06-14T20:19:19+00:00","article_modified_time":"2020-08-17T13:51:29+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg","type":"image\/jpeg"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/","url":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/","name":"Dynamic Calendar in Power BI with CALENDARAUTO() - Unija Smart","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg","datePublished":"2020-06-14T20:19:19+00:00","dateModified":"2020-08-17T13:51:29+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"description":"Calendar is the most important table in Power BI data model. Let's look at how to create a dynamic calendar with CALENDRAUTO() function.","breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG6.jpg","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dynamic-calendar-in-power-bi-with-dax-function-calendarauto\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Dynamic Calendar in Power BI with CALENDARAUTO()"}]},{"@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\/1249"}],"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=1249"}],"version-history":[{"count":5,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1249\/revisions"}],"predecessor-version":[{"id":1598,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1249\/revisions\/1598"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1204"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}