top of page
Search

Stop Repeating DAX: Reusable Time Intelligence Calculation Group with TMDL

  • Jihwan Kim
  • Aug 3
  • 5 min read

In this writing, I’d like to share how I've learned to dramatically accelerate one of the most common and repetitive tasks in Power BI development: implementing time intelligence. For years, I as a Power BI developer have spent countless hours writing dozens, sometimes hundreds, of nearly identical DAX measures. With the recent advancements in Power BI, particularly the introduction of the TMDL View, I can now transform this tedious process into an elegant, reusable, and highly efficient solution.


Link to Microsoft Learn and blogpost




The Scenario: The DAX Repetition Trap


Imagine this common scenario: I’ve just finished building the first version of data model (Direct Lake mode) for my team. It contains 4 base measures, covering Sales Amount, Sales Order Count, Order Quantity, and Total Product Cost. The model is clean, the relationships are solid, and the reports is insightful.

 

Data model (direct lake mode):

ree

Report:

ree


Then, the business comes back with a new request. They want to see Quarter-to-Date (QTD), Year-to-Date (YTD), Previous-Year (PY), Year-over-Year (YoY), and Year-over-Year-percentage (YoY %) for every single one of those 4 base measures.

Suddenly, my task list explodes. I am now facing the daunting prospect of manually creating and maintaining an additional 16 DAX measures. This approach is not only incredibly time-consuming but also a nightmare for governance. A small logic change in the YoY calculation would require me to edit 4 different measures, introducing a high risk of error and inconsistency. There is a far more elegant and scalable solution.




The Solution: Calculation Groups as Reusable Code


Calculation Groups are the definitive answer to this problem. They are powerful, model-level objects that contain a collection of DAX expressions, called calculation items, which can be dynamically applied to any existing measure in my model. Instead of creating 4 different YTD measures, I create a single "YTD" calculation item that can modify any measure I pair it with.   


While Calculation Groups have existed for a while, creating them often required external tools like Tabular Editor.

This is where the new TMDL View in Power BI Desktop becomes a true game-changer. Tabular Model Definition Language (TMDL) is a human-readable, text-based format for defining my entire semantic model. With the TMDL View, I can now define, edit, and apply complex objects like calculation groups using a simple script, directly within Power BI Desktop.   

This transforms calculation groups from a niche, expert-only feature into a portable, shareable, and version-controllable asset—essentially, a code library for my DAX logic. The Power BI community has been buzzing with excitement about this, sharing TMDL scripts for everything from dynamic formatting to time intelligence.




The Time Intelligence Script


Step 01: I do not have any calculation groups in the data model. And Discourage Implicit measures set to TRUE.

ree

Step 02: Here is a production-ready, copy-and-paste TMDL script for a comprehensive time intelligence calculation group. It includes a wide range of common calculations and is designed to be a foundational asset for almost any Power BI model.

Prerequisite: My model must contain a well-formed Date table that has been marked as a date table in the Model view.


createOrReplace

	/// A collection of common time intelligence calculations.
	table 'Time Intelligence'

		calculationGroup
			precedence: 10

			calculationItem Current = SELECTEDMEASURE()

			calculationItem QTD = CALCULATE( SELECTEDMEASURE(), DATESQTD(dimdate[FullDateAlternateKey]))

			calculationItem YTD = CALCULATE( SELECTEDMEASURE(), DATESYTD(dimdate[FullDateAlternateKey]))

			calculationItem PY = CALCULATE( SELECTEDMEASURE(), SAMEPERIODLASTYEAR(dimdate[FullDateAlternateKey]))

			calculationItem YoY =
					
					VAR _current_value = SELECTEDMEASURE()
					VAR _prior_year_value = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(dimdate[FullDateAlternateKey]))
					RETURN _current_value - _prior_year_value

			calculationItem 'YoY %' =
					
					VAR _current_value = SELECTEDMEASURE()
					VAR _prior_year_value = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(dimdate[FullDateAlternateKey]))
					RETURN DIVIDE( _current_value - _prior_year_value, _prior_year_value)

				formatStringDefinition = "0.00%;-0.00%;0.00%"

		column 'Calculation group column'
			dataType: string
			summarizeBy: none
			sourceColumn: Name
			sortByColumn: Ordinal

			annotation SummarizationSetBy = Automatic

		column Ordinal
			dataType: int64
			formatString: 0
			summarizeBy: sum
			sourceColumn: Ordinal

			annotation SummarizationSetBy = Automatic

Step 03: How to Apply the Script - A Step-by-Step Guide

  1. Enable TMDL View: In Power BI Desktop, go to File > Options and settings > Options > Preview features and ensure that TMDL View is checked.  

  2. Open TMDL View: Click the TMDL View icon on the left-hand pane of Power BI Desktop.

  3. Paste the Script: Copy the entire TMDL script above and paste it into an empty script tab in the TMDL View.

  4. Apply Changes: Click the Apply button at the top of the script editor. You will see a notification that the changes have been applied, and a new "Time Intelligence" calculation group will instantly appear in your model's Data pane. -> One thing to note is that it needs to be manually refreshed.

ree
ree
ree

Step 04: How to Use It in a Report - Using my new calculation group is simple:

  1. Create a Matrix visual.

  2. Drag the date hierarchy (e.g., Year and Month from my Dim-Date table) to the Rows field well.

  3. From my new Time Intelligence table, drag the Time Intelligence column to the Columns field well.

  4. Finally, drag any base measures) into the Values field well.


The result is a dynamic and powerful time intelligence report, created with just four base measures.


ree



Best Practices and a New Paradigm


This technique is about more than just saving time; it represents a fundamental shift toward treating semantic models as code. Here are some key insights to keep in mind:

  • Understanding the DAX: The magic behind calculation groups lies in the SELECTEDMEASURE() function. It acts as a placeholder that is dynamically replaced by whatever measure is currently in the filter context (i.e., the measures I placed in the Values field well of my visual).

  • Dynamic Formatting: Notice the YoY % calculation item includes a formatStringDefinition. This allows me to override the base measure's format string, ensuring that my percentages are always displayed correctly without needing to create separate measures just for formatting.

  • Managing Precedence: The precedence property is crucial when I have multiple calculation groups in my model (e.g., one for time intelligence and another for currency conversion). It determines the order in which the calculation groups are applied evaluated first.

  • The "Package Management" Paradigm: TMDL is fostering an open-source ecosystem for reusable model components. Think of this script as a "package" I can install in any model to instantly add time intelligence functionality. This will dramatically accelerate development and promote the adoption of best practices across the team.




Conclusion


By embracing Calculation Groups through the TMDL View, I can eliminate vast amounts of repetitive DAX code, making my models cleaner, more consistent, and far easier to maintain. This approach allows me to build sophisticated analytical capabilities with unprecedented speed and scalability.

I hope this script helps having fun with your data models and frees you up to focus on solving more complex and interesting challenges.

Comments

bottom of page