Power BI TMDL table creation | flip-it.de

Using TMDL, entire environments can be prepared in this way. In this example—which can, of course, also be used in production—a 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’t make sense in a calendar.
You can find Microsoft’s official documentation here, including how to enable and use TMDL:
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-tmdl-view

The script listed below is inserted into the TMDL editor and then executed.

createOrReplace

	table 'Dim Date'
		lineageTag: a4b7e2bb-8b6d-46fb-88ec-8c5f54c97be3

		column Date
			formatString: General Date
			lineageTag: b2b21c5c-19e2-46d5-b774-3df902dba1ad
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date]

			annotation SummarizationSetBy = Automatic

		column Date_Year
			formatString: 0
			lineageTag: 0c59df1d-116c-4c75-a43d-940d7a15a48f
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_Year]

			annotation SummarizationSetBy = Automatic

		column Date_Month
			formatString: 0
			lineageTag: fa11bdf6-0009-40c8-acd5-b7458a8c1fd1
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_Month]

			annotation SummarizationSetBy = Automatic

		column Month_Number
			formatString: 0
			lineageTag: 3fa0bc05-362a-4fbf-ac76-e35a021619e0
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month_Number]

			annotation SummarizationSetBy = User

		column Month_NumberText
			lineageTag: 5c7d0462-f443-4266-8412-98d9c188a0c3
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month_NumberText]

			annotation SummarizationSetBy = Automatic

		column Month_Name
			lineageTag: 2156582e-0a3e-4ca4-94f3-caa7ea03732f
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month_Name]
			sortByColumn: Date_Month

			annotation SummarizationSetBy = Automatic

		column Month_ShortName
			lineageTag: 99d82088-c77e-4553-941e-b9a9969cbeb4
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month_ShortName]

			annotation SummarizationSetBy = Automatic

		column Date_Day
			formatString: 0
			lineageTag: 75efe114-9be2-42cf-ae1f-3d3538767dc4
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_Day]

			annotation SummarizationSetBy = Automatic

		column Date_WeekdayNumber
			formatString: 0
			lineageTag: aa7e0a9d-dfdb-4903-82a3-600a570ac2ec
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_WeekdayNumber]

			annotation SummarizationSetBy = User

		column Date_DayName
			lineageTag: 4bc41ff3-5439-414e-b9d6-d60cefcbe98c
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_DayName]
			sortByColumn: Date_WeekdayNumber

			annotation SummarizationSetBy = Automatic

		column Date_DayShort
			lineageTag: 2f0b658e-1719-4ce6-b9e8-a275d3e93806
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date_DayShort]

			annotation SummarizationSetBy = Automatic

		column Quarter_Number
			formatString: 0
			lineageTag: b18c8679-391f-4155-8283-00497e8eada6
			summarizeBy: none
			isNameInferred
			sourceColumn: [Quarter_Number]

			annotation SummarizationSetBy = User

		column Quarter_Label
			lineageTag: 1abcc42f-c19e-4197-8ec8-3851d879b222
			summarizeBy: none
			isNameInferred
			sourceColumn: [Quarter_Label]

			annotation SummarizationSetBy = Automatic

		column Quarter_Short
			lineageTag: f28d48cc-9c8d-4b51-957a-4cb02640b4da
			summarizeBy: none
			isNameInferred
			sourceColumn: [Quarter_Short]

			annotation SummarizationSetBy = Automatic

		column Week_Number
			formatString: 0
			lineageTag: 38c9769f-0ffd-45b9-858f-22ec2fff7ad9
			summarizeBy: none
			isNameInferred
			sourceColumn: [Week_Number]

			annotation SummarizationSetBy = Automatic

		column ISO_WeekNumber
			formatString: 0
			lineageTag: 18583e14-bbfe-4862-89e8-c14415ed2cdc
			summarizeBy: none
			isNameInferred
			sourceColumn: [ISO_WeekNumber]

			annotation SummarizationSetBy = User

		column ISO_Year
			formatString: 0
			lineageTag: c01eea79-c4fd-4186-b40d-c918f0738699
			summarizeBy: none
			isNameInferred
			sourceColumn: [ISO_Year]

			annotation SummarizationSetBy = Automatic

		column ISO_YearWeek
			formatString: 0
			lineageTag: 6676557f-0e50-4d66-83a8-856367bd2e5b
			summarizeBy: none
			isNameInferred
			sourceColumn: [ISO_YearWeek]

			annotation SummarizationSetBy = Automatic

		column Month_YearLabel
			lineageTag: b9584b5a-277b-430f-bc56-6e2289504468
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month_YearLabel]

			annotation SummarizationSetBy = Automatic

		column Year_MonthLabel
			lineageTag: a1a78970-3d90-49b1-a92b-1ea4b944eb90
			summarizeBy: none
			isNameInferred
			sourceColumn: [Year_MonthLabel]

			annotation SummarizationSetBy = Automatic

		column YearMonth_Key
			formatString: 0
			lineageTag: fbd52889-9af7-4018-aaae-4e20cbcaa755
			summarizeBy: none
			isNameInferred
			sourceColumn: [YearMonth_Key]

			annotation SummarizationSetBy = Automatic

		column Period_StartMonth
			formatString: General Date
			lineageTag: f7d627fc-d538-41c3-b4e5-c66a0369d983
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_StartMonth]

			annotation SummarizationSetBy = Automatic

		column Period_EndMonth
			formatString: General Date
			lineageTag: 96cda275-25a0-4246-8b8f-78e2c0ad4810
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_EndMonth]

			annotation SummarizationSetBy = Automatic

		column Period_StartQuarter
			formatString: General Date
			lineageTag: 54b54bf3-64d1-4000-89be-d87d08c5a37f
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_StartQuarter]

			annotation SummarizationSetBy = Automatic

		column Period_EndQuarter
			formatString: General Date
			lineageTag: 3910a973-1fde-40e5-b7b2-8eaac72ab1f2
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_EndQuarter]

			annotation SummarizationSetBy = Automatic

		column Period_StartYear
			formatString: General Date
			lineageTag: d63b94c8-2952-4b46-9264-79d3c86cf18a
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_StartYear]

			annotation SummarizationSetBy = Automatic

		column Period_EndYear
			formatString: General Date
			lineageTag: 9b0ee762-7e6a-4ee0-8389-35c6a7380ffc
			summarizeBy: none
			isNameInferred
			sourceColumn: [Period_EndYear]

			annotation SummarizationSetBy = Automatic

		column Flag_IsWorkday
			formatString: """TRUE"";""TRUE"";""FALSE"""
			lineageTag: 06e7d706-7342-484a-9c08-fd9d840e464b
			summarizeBy: none
			isNameInferred
			sourceColumn: [Flag_IsWorkday]

			annotation SummarizationSetBy = Automatic

		column Flag_IsWeekend
			formatString: """TRUE"";""TRUE"";""FALSE"""
			lineageTag: 9ce4342a-f799-4d3b-8582-025001129da6
			summarizeBy: none
			isNameInferred
			sourceColumn: [Flag_IsWeekend]

			annotation SummarizationSetBy = Automatic

		column Display_WeekdayLabel
			lineageTag: c0881c01-8b0c-4f36-82c5-96d8a94558f8
			summarizeBy: none
			isNameInferred
			sourceColumn: [Display_WeekdayLabel]

			annotation SummarizationSetBy = Automatic

		partition 'Dim Date' = calculated
			mode: import
			source = ```
					
					ADDCOLUMNS (
					    CALENDARAUTO(),
					    "Date_Year", YEAR([Date]),
					    "Date_Month", MONTH([Date]),
					    "Month_Number", MONTH([Date]),
					    "Month_NumberText", FORMAT([Date], "MM"),
					    "Month_Name", FORMAT([Date], "MMMM"),
					    "Month_ShortName", FORMAT([Date], "MMM"),
					    "Date_Day", DAY([Date]),
					    "Date_WeekdayNumber", WEEKDAY([Date], 2),
					    "Date_DayName", FORMAT([Date], "dddd"),
					    "Date_DayShort", FORMAT([Date], "ddd"),
					    "Quarter_Number", QUARTER([Date]),
					    "Quarter_Label", "Q" & QUARTER([Date]),
					    "Quarter_Short", "Q" & QUARTER([Date]),
					    "Week_Number", WEEKNUM([Date], 2),
					    "ISO_WeekNumber",
					        VAR dow = WEEKDAY([Date], 2)
					        VAR thursday = [Date] + (4 - dow)
					        RETURN WEEKNUM(thursday, 21),
					    "ISO_Year",
					        VAR dow2 = WEEKDAY([Date], 2)
					        VAR thursday2 = [Date] + (4 - dow2)
					        RETURN YEAR(thursday2),
					    "ISO_YearWeek",
					        VAR dow3 = WEEKDAY([Date], 2)
					        VAR thursday3 = [Date] + (4 - dow3)
					        RETURN YEAR(thursday3) * 100 + WEEKNUM(thursday3, 21),
					    "Month_YearLabel", FORMAT([Date], "MMM yyyy"),
					    "Year_MonthLabel", FORMAT([Date], "yyyy-MM"),
					    "YearMonth_Key", YEAR([Date]) * 100 + MONTH([Date]),
					    "Period_StartMonth", DATE(YEAR([Date]), MONTH([Date]), 1),
					    "Period_EndMonth", EOMONTH([Date], 0),
					    "Period_StartQuarter", DATE(YEAR([Date]), (QUARTER([Date]) - 1) * 3 + 1, 1),
					    "Period_EndQuarter", EOMONTH(DATE(YEAR([Date]), (QUARTER([Date]) - 1) * 3 + 3, 1), 0),
					    "Period_StartYear", DATE(YEAR([Date]), 1, 1),
					    "Period_EndYear", DATE(YEAR([Date]), 12, 31),
					    "Flag_IsWorkday", WEEKDAY([Date], 2) < 6,
					    "Flag_IsWeekend", WEEKDAY([Date], 2) >= 6,
					    "Display_WeekdayLabel", "Wk " & WEEKNUM([Date], 2) & " - " & FORMAT([Date], "dddd")
					)
					
					```

		annotation PBI_Id = ab77d62828864343b4cab2ae398df43d

As a result, the Dim Date table is available in the model. To populate the table, the CALENDARAUTO() function is used. The data model is scanned for columns of type DATE; the smallest and largest date values are defined as the start and end dates. The date table therefore contains values between these two bounds.