Create Calendar Table in One Click using TMDL View in Power BI

Create Calendar Table in One Click using TMDL View in Power BI


Creating a robust, reusable Calendar Table is one of the most essential steps in any data modeling process. And now with the introduction of TMDL (Tabular Model Definition Language) in Power BI, this process becomes not just easier but faster than ever—literally in one click.

In this blog, I’ll show you how you can use the TMDL View in Power BI to instantly create a rich calendar table with fiscal year handling, week numbers, month names, and more. You can also check out the step-by-step walkthrough video I’ve created for better visual understanding 📹.

🚀 What is TMDL View in Power BI?

TMDL (Tabular Model Definition Language) is a new declarative way to define tabular models in Power BI using code—making your model components (tables, columns, measures, partitions) more manageable, auditable, and reusable.


With TMDL support enabled, you can now:

  • View and edit model metadata in human-readable format

  • Create model objects directly using code (like calculated tables, measures, etc.)

  • Track and version model changes easily using source control

🧠 Why Use a Calendar Table?

Having a dedicated Calendar Table enables:

  • Proper time intelligence (e.g., YTD, MTD, QTD calculations)

  • Consistent filtering and grouping across visuals

  • Fiscal year handling

  • Easier customizations for weeks, months, quarters

⚙️ How to Create Calendar Table using TMDL View?

Here’s a sample TMDL definition for a Calendar Table you can plug into the TMDL view.

🧾 TMDL Code:

createOrReplace
    table Calendar
        lineageTag: 3984c24d-ea4e-4741-8685-c8c1b917b9cc

        column Date
            formatString: General Date
            lineageTag: a1dbc3cf-c187-4c92-910c-8d706785fd4e
            summarizeBy: none
            sourceColumn: [Date]

        column Year
            formatString: 0
            sourceColumn: [Year]

        column YearNumber
            formatString: 0
            sourceColumn: [YearNumber]

        column QuarterOfYear
            formatString: 0
            sourceColumn: [QuarterOfYear]

        column MonthOfYear
            formatString: 0
            sourceColumn: [MonthOfYear]

        column DayOfMonth
            formatString: 0
            sourceColumn: [DayOfMonth]

        column DateInt
            formatString: 0
            sourceColumn: [DateInt]

        column MonthName
            sourceColumn: [MonthName]

        column MonthInCalendar
            sourceColumn: [MonthInCalendar]

        column QuarterInCalendar
            sourceColumn: [QuarterInCalendar]

        column DayInWeek
            formatString: 0
            sourceColumn: [DayInWeek]

        column DayOfWeekName
            sourceColumn: [DayOfWeekName]

        column WeekNumber
            formatString: 0
            sourceColumn: [Week Number]

        column MonthnYear
            formatString: 0
            sourceColumn: [MonthnYear]

        column QuarternYear
            formatString: 0
            sourceColumn: [QuarternYear]

        column ShortYear
            sourceColumn: [ShortYear]

        column FY
            sourceColumn: [FY]

        partition Calendar = calculated
            mode: import
            source = ```
                VAR StartDate = DATE(2025,1,1)
                VAR EndDate = DATE(2025,12,31)
                VAR FYStartMonth = 4
                VAR Dates = CALENDAR(StartDate, EndDate)

                RETURN
                ADDCOLUMNS(
                    Dates,
                    "Year", FORMAT([Date], "yyyy"),
                    "YearNumber", YEAR([Date]),
                    "QuarterOfYear", QUARTER([Date]),
                    "MonthOfYear", MONTH([Date]),
                    "DayOfMonth", DAY([Date]),
                    "DateInt", YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date]),
                    "MonthName", FORMAT([Date], "MMMM"),
                    "MonthInCalendar", FORMAT([Date], "MMM") & " " & FORMAT([Date], "yyyy"),
                    "QuarterInCalendar", "Q" & QUARTER([Date]) & " " & FORMAT([Date], "yyyy"),
                    "DayInWeek", WEEKDAY([Date], 1) - 1,
                    "DayOfWeekName", FORMAT([Date], "dddd"),
                    "Week Number", WEEKNUM([Date], 2),
                    "MonthnYear", YEAR([Date]) * 10000 + MONTH([Date]) * 100,
                    "QuarternYear", YEAR([Date]) * 10000 + QUARTER([Date]) * 100,
                    "ShortYear", RIGHT(YEAR([Date]), 2),
                    "FY", "FY" & IF(MONTH([Date]) >= FYStartMonth, RIGHT(YEAR([Date]) + 1, 2), RIGHT(YEAR([Date]), 2))
                )
            ```

🔧 Steps to Implement:

  1. ✅ Enable TMDL View in Power BI Desktop (Preview feature)

  2. ✅ Navigate to TMDL View in Power BI Desktop and Create a New Script.

  3. ✅ Paste the above code into the TMDL Script Interface (Refer below screenshot)

  4. ✅ Click on Apply button on top left.

  5. ✅ Calendar table will get created, click on the refresh now button.


  6. Explore the calendar table from the Data view


  7. Note: If you want to change the date range or FY Start month then you can change it in the variables created in line 72,73 & 74. (Screenshot attached)

🎥 Step-by-Step Video Guide

I've also created a detailed video tutorial showing each step from enabling TMDL to using the Calendar table in your reports.

👉 Watch now on my YouTube channel [ANMOLPOWERBICORNER]
🔗 Video Link: https://youtu.be/ogEcYqf1yJI

📌 Final Thoughts

TMDL is a game-changer for Power BI developers. With just one snippet of code, you now have a full-featured, reusable Calendar Table ready to go! This will help you:

  • Save time ⏳

  • Maintain consistency across reports 📊

  • Simplify versioning and collaboration using Git ✅

💬 Have questions or want to connect? Feel free to reach out to me on Anmol Malviya.

📍 Follow my blog series for more such Power BI TMDL tips and tricks!

Comments

Post a Comment

Popular posts from this blog

Embedding Power BI Reports in Websites and Applications: A Complete Guide

Embedding Power BI Reports in Websites and Applications: A Complete Guide Introduction Embedding Power BI reports in websites and applications is a powerful way to share interactive data visualizations with a broader audience. Whether you want to embed a report for internal users, customers, or the general public, Power BI provides several options to make this process seamless. In this guide, we will walk you through the various embedding options and the steps required to embed Power BI reports in your web pages or custom apps. 1. Why Embed Power BI Reports? Embedding Power BI reports offers several benefits: Increased Accessibility : Allow users to view and interact with reports without needing to access Power BI Service directly. Enhanced User Experience : Integrate data insights directly within your application or website for a seamless user experience. Improved Data Sharing : Share valuable insights with users outside your organization, such as clients or partners, in a secure and ...
Read more

Import Microsoft Planner Data into Power BI Using Power Automate and SharePoint

Image
  In this guide, we’ll cover how to bring task data from Microsoft Planner into Power BI for analysis, using Power Automate and SharePoint as intermediaries. Since Power BI currently lacks a direct Planner connector, Power Automate lets us fetch the data from Planner and save it to SharePoint as a JSON file, enabling Power BI to access it on a scheduled basis. Prerequisites Before you begin, ensure you have: Access to Microsoft Planner  – to view and retrieve Planner task data. Power Automate License  – access to create and run Power Automate flows. SharePoint Access  – a SharePoint site to store JSON files. Using SharePoint simplifies the setup, as it doesn’t require a premium Power BI or Power Automate license. Power BI Desktop  – to set up and configure the JSON data connection and publish the report. Microsoft 365 Account  – for authentication, as we’ll be connecting Power BI to SharePoint through your organization’s Microsoft 365 credentials. Step 1: S...
Read more

Difference between Append and Merge in Power BI

Image
Append vs. Merge in Power BI: Key Differences and Use Cases Introduction When working with data in Power BI, combining datasets is often essential for creating comprehensive reports. Power BI offers two primary methods for combining tables: Append and Merge . While both are used to consolidate data from multiple sources, they work in different ways and are suited for different scenarios. In this blog, we will explore the key differences between Append and Merge in Power BI, along with practical examples to help you choose the right approach for your data needs. 1. What is Append in Power BI? Append in Power BI is used to combine rows from two or more tables into a single table. The structure of the tables being appended should be similar (i.e., they should have the same columns), as Append simply stacks the rows on top of each other. Key Features of Append: Row Combination : Append combines rows from multiple tables, one after the other. No Column Matching : Columns are not matched b...
Read more