Power BI – Create a calendar using TMDL

TMDL is short for Tabular Model Definition Language in Power BI desktop. It allows us to script definitions and changes to a Power BI model. In this post I use TMDL to quickly create a calendar for a report that includes column options such as sort by, formatting and hidden columns.

Calendar Table

Nearly every report I create includes a calendar table. Every Power BI introduction course I run I send delegates to SQLBI site to grab the code for a simple calendar. Using a few example exercises I explain column sorting for the months, hiding the columns used for sorting and formatting the date columns. Tasks we all do every new report.

Just to be clear the code I usually use comes from one of these links, in this post I’ve used the second one.

Turning on TMDL View

At the point of publishing this post (Jun 2025) TMDL view is still in preview so it need be turned on. From the File tab select, Options and settings and then Options to open the dialog. Then under Preview features put a tick next to TMDL view. This will add a new button on the top left views menu.

TMDL View

This view allows you to define tables, columns, relationships and measures. You can drag a Power BI item into the view from the data pane to see the definition. Bu this post is not to teach TMDL but to show one use of it. More information can be found at https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-tmdl-view

Adding the Calendar

Another use is to use code to create a calendar table. I created the original code by creating calendar in a report and making all the adjustments I would usually do and then dragged the calendar table onto the TMDL window. So now the code includes the adjustments mentioned at the start of this post. Then I go to a report without a calendar and paste in the code below in the TMDL view. Then click Apply. The table will appear in the Data pane but possibly with red exclamation mark. There also might be a message stating a calculated table needs refreshing. If that appears click the Refresh now button that appears.

Copy Code

createOrReplace

	table Calendar
		lineageTag: a8e10f84-8296-4cf3-bda3-412811e34790

		column Date
			formatString: General Date
			lineageTag: b0b99eff-688f-4526-81d2-8afc25541413
			summarizeBy: none
			isNameInferred
			sourceColumn: [Date]

			annotation SummarizationSetBy = Automatic

		column Year
			formatString: 0
			lineageTag: 4ad002b0-e801-4457-865d-8766808bacb9
			summarizeBy: none
			isNameInferred
			sourceColumn: [Year]

			annotation SummarizationSetBy = User

		column 'Month Number'
			isHidden
			formatString: 0
			lineageTag: e14ae615-2ba5-4e4a-ad44-ff0fcb4fb3ed
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month Number]

			annotation SummarizationSetBy = User

		column Month
			lineageTag: e9c8caaf-f192-4478-86bd-d811116a5ac1
			summarizeBy: none
			isNameInferred
			sourceColumn: [Month]
			sortByColumn: 'Month Number'

			annotation SummarizationSetBy = Automatic

		column 'Year Month Number'
			isHidden
			formatString: 0
			lineageTag: 9c81640a-3399-4b97-87dd-884390419cb4
			summarizeBy: none
			isNameInferred
			sourceColumn: [Year Month Number]

			annotation SummarizationSetBy = User

		column 'Year Month'
			lineageTag: a2d59320-d499-43cf-a365-44c1f33b8ed4
			summarizeBy: none
			isNameInferred
			sourceColumn: [Year Month]
			sortByColumn: 'Year Month Number'

			annotation SummarizationSetBy = Automatic

		partition Calendar = calculated
			mode: import
			source =
					
					VAR MinYear = 2025
					VAR MaxYear = 2025
					VAR BaseCalendar =
					    CALENDAR ( DATE ( MinYear, 1, 1 ), DATE ( MaxYear, 12, 31 ) )
					RETURN
					    GENERATE (
					        BaseCalendar,
					        VAR BaseDate = [Date]
					        VAR YearDate = YEAR ( BaseDate )
					        VAR MonthNumber = MONTH ( BaseDate )
					        VAR MonthName = FORMAT ( BaseDate, "mmmm" )
					        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
					        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
					        RETURN ROW (
					            "Year", YearDate,
					            "Month Number", MonthNumber,
					            "Month", MonthName,
					            "Year Month Number", YearMonthNumber,
					            "Year Month", YearMonthName
					        )
					    )

		annotation PBI_Id = 0bd3b6910f2844c8b21afe01ffb36109

The report now has a calendar table matching your original calendar.

Conclusion

Being able to use TMDL will allow me to build a library of code snippets to quickly build the report elements I regularly build. This is an exciting extra tool for the toolbox. I have already started a github library of snippets I think will be useful. Not ready to make that public yet but I will.

Power BI Series

Categories:Power BI

Tags:2025-2026

Hat Full of Data