{"id":1533,"date":"2025-10-07T13:38:25","date_gmt":"2025-10-07T11:38:25","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1533"},"modified":"2025-10-07T13:38:25","modified_gmt":"2025-10-07T11:38:25","slug":"power-bi-tmdl-table-creation","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1533","title":{"rendered":"Power BI TMDL table creation"},"content":{"rendered":"\n<p>Using TMDL, entire environments can be prepared in this way. In this example\u2014which can, of course, also be used in production\u2014a complete date table is created exactly as it is typically used. Months and days are sorted in correct calendar order. Numeric fields are not aggregated, because aggregation generally doesn\u2019t make sense in a calendar.<br>You can find Microsoft\u2019s official documentation here, including how to enable and use TMDL:<br><a href=\"https:\/\/learn.microsoft.com\/en-us\/power-bi\/transform-model\/desktop-tmdl-view\">https:\/\/learn.microsoft.com\/en-us\/power-bi\/transform-model\/desktop-tmdl-view<\/a><\/p>\n\n\n\n<p>The script listed below is inserted into the TMDL editor and then executed.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2025\/10\/image.png\"><img decoding=\"async\" loading=\"lazy\" width=\"940\" height=\"404\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2025\/10\/image.png\" alt=\"\" class=\"wp-image-1534\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2025\/10\/image.png 940w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2025\/10\/image-300x129.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2025\/10\/image-768x330.png 768w\" sizes=\"(max-width: 940px) 100vw, 940px\" \/><\/a><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>createOrReplace\r\n\r\n\ttable 'Dim Date'\r\n\t\tlineageTag: a4b7e2bb-8b6d-46fb-88ec-8c5f54c97be3\r\n\r\n\t\tcolumn Date\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: b2b21c5c-19e2-46d5-b774-3df902dba1ad\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Date_Year\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 0c59df1d-116c-4c75-a43d-940d7a15a48f\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_Year]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Date_Month\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: fa11bdf6-0009-40c8-acd5-b7458a8c1fd1\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_Month]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Month_Number\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 3fa0bc05-362a-4fbf-ac76-e35a021619e0\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Month_Number]\r\n\r\n\t\t\tannotation SummarizationSetBy = User\r\n\r\n\t\tcolumn Month_NumberText\r\n\t\t\tlineageTag: 5c7d0462-f443-4266-8412-98d9c188a0c3\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Month_NumberText]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Month_Name\r\n\t\t\tlineageTag: 2156582e-0a3e-4ca4-94f3-caa7ea03732f\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Month_Name]\r\n\t\t\tsortByColumn: Date_Month\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Month_ShortName\r\n\t\t\tlineageTag: 99d82088-c77e-4553-941e-b9a9969cbeb4\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Month_ShortName]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Date_Day\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 75efe114-9be2-42cf-ae1f-3d3538767dc4\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_Day]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Date_WeekdayNumber\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: aa7e0a9d-dfdb-4903-82a3-600a570ac2ec\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_WeekdayNumber]\r\n\r\n\t\t\tannotation SummarizationSetBy = User\r\n\r\n\t\tcolumn Date_DayName\r\n\t\t\tlineageTag: 4bc41ff3-5439-414e-b9d6-d60cefcbe98c\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_DayName]\r\n\t\t\tsortByColumn: Date_WeekdayNumber\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Date_DayShort\r\n\t\t\tlineageTag: 2f0b658e-1719-4ce6-b9e8-a275d3e93806\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Date_DayShort]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Quarter_Number\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: b18c8679-391f-4155-8283-00497e8eada6\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Quarter_Number]\r\n\r\n\t\t\tannotation SummarizationSetBy = User\r\n\r\n\t\tcolumn Quarter_Label\r\n\t\t\tlineageTag: 1abcc42f-c19e-4197-8ec8-3851d879b222\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Quarter_Label]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Quarter_Short\r\n\t\t\tlineageTag: f28d48cc-9c8d-4b51-957a-4cb02640b4da\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Quarter_Short]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Week_Number\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 38c9769f-0ffd-45b9-858f-22ec2fff7ad9\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Week_Number]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn ISO_WeekNumber\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 18583e14-bbfe-4862-89e8-c14415ed2cdc\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;ISO_WeekNumber]\r\n\r\n\t\t\tannotation SummarizationSetBy = User\r\n\r\n\t\tcolumn ISO_Year\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: c01eea79-c4fd-4186-b40d-c918f0738699\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;ISO_Year]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn ISO_YearWeek\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: 6676557f-0e50-4d66-83a8-856367bd2e5b\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;ISO_YearWeek]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Month_YearLabel\r\n\t\t\tlineageTag: b9584b5a-277b-430f-bc56-6e2289504468\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Month_YearLabel]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Year_MonthLabel\r\n\t\t\tlineageTag: a1a78970-3d90-49b1-a92b-1ea4b944eb90\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Year_MonthLabel]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn YearMonth_Key\r\n\t\t\tformatString: 0\r\n\t\t\tlineageTag: fbd52889-9af7-4018-aaae-4e20cbcaa755\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;YearMonth_Key]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_StartMonth\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: f7d627fc-d538-41c3-b4e5-c66a0369d983\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_StartMonth]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_EndMonth\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: 96cda275-25a0-4246-8b8f-78e2c0ad4810\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_EndMonth]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_StartQuarter\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: 54b54bf3-64d1-4000-89be-d87d08c5a37f\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_StartQuarter]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_EndQuarter\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: 3910a973-1fde-40e5-b7b2-8eaac72ab1f2\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_EndQuarter]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_StartYear\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: d63b94c8-2952-4b46-9264-79d3c86cf18a\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_StartYear]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Period_EndYear\r\n\t\t\tformatString: General Date\r\n\t\t\tlineageTag: 9b0ee762-7e6a-4ee0-8389-35c6a7380ffc\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Period_EndYear]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Flag_IsWorkday\r\n\t\t\tformatString: \"\"\"TRUE\"\";\"\"TRUE\"\";\"\"FALSE\"\"\"\r\n\t\t\tlineageTag: 06e7d706-7342-484a-9c08-fd9d840e464b\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Flag_IsWorkday]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Flag_IsWeekend\r\n\t\t\tformatString: \"\"\"TRUE\"\";\"\"TRUE\"\";\"\"FALSE\"\"\"\r\n\t\t\tlineageTag: 9ce4342a-f799-4d3b-8582-025001129da6\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Flag_IsWeekend]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tcolumn Display_WeekdayLabel\r\n\t\t\tlineageTag: c0881c01-8b0c-4f36-82c5-96d8a94558f8\r\n\t\t\tsummarizeBy: none\r\n\t\t\tisNameInferred\r\n\t\t\tsourceColumn: &#91;Display_WeekdayLabel]\r\n\r\n\t\t\tannotation SummarizationSetBy = Automatic\r\n\r\n\t\tpartition 'Dim Date' = calculated\r\n\t\t\tmode: import\r\n\t\t\tsource = ```\r\n\t\t\t\t\t\r\n\t\t\t\t\tADDCOLUMNS (\r\n\t\t\t\t\t    CALENDARAUTO(),\r\n\t\t\t\t\t    \"Date_Year\", YEAR(&#91;Date]),\r\n\t\t\t\t\t    \"Date_Month\", MONTH(&#91;Date]),\r\n\t\t\t\t\t    \"Month_Number\", MONTH(&#91;Date]),\r\n\t\t\t\t\t    \"Month_NumberText\", FORMAT(&#91;Date], \"MM\"),\r\n\t\t\t\t\t    \"Month_Name\", FORMAT(&#91;Date], \"MMMM\"),\r\n\t\t\t\t\t    \"Month_ShortName\", FORMAT(&#91;Date], \"MMM\"),\r\n\t\t\t\t\t    \"Date_Day\", DAY(&#91;Date]),\r\n\t\t\t\t\t    \"Date_WeekdayNumber\", WEEKDAY(&#91;Date], 2),\r\n\t\t\t\t\t    \"Date_DayName\", FORMAT(&#91;Date], \"dddd\"),\r\n\t\t\t\t\t    \"Date_DayShort\", FORMAT(&#91;Date], \"ddd\"),\r\n\t\t\t\t\t    \"Quarter_Number\", QUARTER(&#91;Date]),\r\n\t\t\t\t\t    \"Quarter_Label\", \"Q\" &amp; QUARTER(&#91;Date]),\r\n\t\t\t\t\t    \"Quarter_Short\", \"Q\" &amp; QUARTER(&#91;Date]),\r\n\t\t\t\t\t    \"Week_Number\", WEEKNUM(&#91;Date], 2),\r\n\t\t\t\t\t    \"ISO_WeekNumber\",\r\n\t\t\t\t\t        VAR dow = WEEKDAY(&#91;Date], 2)\r\n\t\t\t\t\t        VAR thursday = &#91;Date] + (4 - dow)\r\n\t\t\t\t\t        RETURN WEEKNUM(thursday, 21),\r\n\t\t\t\t\t    \"ISO_Year\",\r\n\t\t\t\t\t        VAR dow2 = WEEKDAY(&#91;Date], 2)\r\n\t\t\t\t\t        VAR thursday2 = &#91;Date] + (4 - dow2)\r\n\t\t\t\t\t        RETURN YEAR(thursday2),\r\n\t\t\t\t\t    \"ISO_YearWeek\",\r\n\t\t\t\t\t        VAR dow3 = WEEKDAY(&#91;Date], 2)\r\n\t\t\t\t\t        VAR thursday3 = &#91;Date] + (4 - dow3)\r\n\t\t\t\t\t        RETURN YEAR(thursday3) * 100 + WEEKNUM(thursday3, 21),\r\n\t\t\t\t\t    \"Month_YearLabel\", FORMAT(&#91;Date], \"MMM yyyy\"),\r\n\t\t\t\t\t    \"Year_MonthLabel\", FORMAT(&#91;Date], \"yyyy-MM\"),\r\n\t\t\t\t\t    \"YearMonth_Key\", YEAR(&#91;Date]) * 100 + MONTH(&#91;Date]),\r\n\t\t\t\t\t    \"Period_StartMonth\", DATE(YEAR(&#91;Date]), MONTH(&#91;Date]), 1),\r\n\t\t\t\t\t    \"Period_EndMonth\", EOMONTH(&#91;Date], 0),\r\n\t\t\t\t\t    \"Period_StartQuarter\", DATE(YEAR(&#91;Date]), (QUARTER(&#91;Date]) - 1) * 3 + 1, 1),\r\n\t\t\t\t\t    \"Period_EndQuarter\", EOMONTH(DATE(YEAR(&#91;Date]), (QUARTER(&#91;Date]) - 1) * 3 + 3, 1), 0),\r\n\t\t\t\t\t    \"Period_StartYear\", DATE(YEAR(&#91;Date]), 1, 1),\r\n\t\t\t\t\t    \"Period_EndYear\", DATE(YEAR(&#91;Date]), 12, 31),\r\n\t\t\t\t\t    \"Flag_IsWorkday\", WEEKDAY(&#91;Date], 2) &lt; 6,\r\n\t\t\t\t\t    \"Flag_IsWeekend\", WEEKDAY(&#91;Date], 2) >= 6,\r\n\t\t\t\t\t    \"Display_WeekdayLabel\", \"Wk \" &amp; WEEKNUM(&#91;Date], 2) &amp; \" - \" &amp; FORMAT(&#91;Date], \"dddd\")\r\n\t\t\t\t\t)\r\n\t\t\t\t\t\r\n\t\t\t\t\t```\r\n\r\n\t\tannotation PBI_Id = ab77d62828864343b4cab2ae398df43d\r\n\r<\/code><\/pre>\n\n\n\n<p>As a result, the <strong>Dim Date<\/strong> table is available in the model. To populate the table, the <strong>CALENDARAUTO()<\/strong> function is used. The data model is scanned for columns of type <strong>DATE<\/strong>; the smallest and largest date values are defined as the start and end dates. The date table therefore contains values between these two bounds.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Using TMDL, entire environments can be prepared in this way. In this example\u2014which can, of course, also be used in production\u2014a complete date table is created exactly as it is typically used. Months and days are sorted in correct calendar &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1533\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1533"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1533"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1533\/revisions"}],"predecessor-version":[{"id":1535,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1533\/revisions\/1535"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}