With TMDL (Tabular Model Definition Language), model creation can be carried out faster and in a more standardized way by storing code definitions as scripts. For example, a frequently required date dimension can be stored as a script with all fields and attributes needed for implementation. This allows the table to be created simply by executing the script.

In this entry, I demonstrate how quickly and easily you can use TMDL to create a date table and a calculation group around a fact table, based on a simple measure. This enables you to save time by generating the relevant measures with just a few clicks.

Okay, let’s take a look at the „data model“:

Currently, there is only a sales table, and a corresponding dimension references this facts table. Additionally, there is a spreadsheet containing relevant key figures. This spreadsheet contains only one simple key figure with the following code:

Sum Rev = SUM(sales[Revenue])

This key figure adds a column to display the revenue.

Now we need a date dimension. This dimension should contain the columns required by the organization. Creating this manually would be quite time-consuming and error-prone. Therefore, we can reference a date dimension in TMDL format. You can find a description of how to create such a table with TMDL code at the following link: https://www.flip-design.de/?p=1533

Since the table is currently empty, the data basis needs to be updated here:

The table containing the facts can then be generated based on this field:

This allows for the creation of simple time visualizations.

To implement the key performance indicators (KPIs) that are frequently needed, a calculation group is a good option. You’ll find a corresponding TMDL code script below. This script would create a calculation group called Time Intelligence.

createOrReplace

    table 'Time Intelligence'
        lineageTag: 00991730-2d3c-46d8-a8c6-2427a67e6684

        calculationGroup

            calculationItem Raw = SELECTEDMEASURE()

            calculationItem MTD = ```
                    CALCULATE( SELECTEDMEASURE(), DATESMTD( 'Dim Date'[Date] ) )
                    
                    ```

            calculationItem QTD = ```
                    CALCULATE( SELECTEDMEASURE(), DATESQTD( 'Dim Date'[Date] ) )
                    
                    ```

            calculationItem YTD = ```
                    CALCULATE( SELECTEDMEASURE(), DATESYTD( 'Dim Date'[Date] ) )
                    
                    ```

            calculationItem PM = ```
                    CALCULATE( SELECTEDMEASURE(), DATEADD( 'Dim Date'[Date], -1, MONTH ) )
                    
                    ```

            calculationItem PQ = ```
                    CALCULATE( SELECTEDMEASURE(), DATEADD( 'Dim Date'[Date], -1, QUARTER ) )
                    
                    ```

            calculationItem PY = CALCULATE( SELECTEDMEASURE(), DATEADD( 'Dim Date'[Date], -1, YEAR ) )

            calculationItem PYTD = ```
                    CALCULATE(
                        SELECTEDMEASURE(),
                        DATESYTD( DATEADD( 'Dim Date'[Date], -1, YEAR ) )
                    )
                    
                    ```

        column 'Time Intelligence'
            dataType: string
            lineageTag: 791f8c80-4d7b-4dbb-af95-7377fdc5bed4
            summarizeBy: none
            sourceColumn: Name
            sortByColumn: Ordinal

            annotation SummarizationSetBy = Automatic

        column Ordinal
            dataType: int64
            formatString: 0
            lineageTag: 583594ac-4aa0-45d4-a8e4-ed7fd29e82e3
            summarizeBy: sum
            sourceColumn: Ordinal

            annotation SummarizationSetBy = Automatic

If you run this script, you will usually get an error message:

This message simply indicates that the property preventing the creation of implicit measures has not yet been deactivated in the model. This property is typically deactivated when creating calculation groups, but the TMDL Editor cannot yet do this, and the property cannot be configured in the desktop version either. To avoid relying on external programs like the Tabular Editor, it’s helpful to create a calculation group once and then delete it. This deactivates the property, which applies to the entire model.

Now that this has been confirmed and the new calculation group has been removed, the TMDL script can be run without any problems.

The new calculation group is now available in the model, and corresponding visualizations can be created with it.