Creating essential time intelligence measures like YoY comparisons manually is tedious and error-prone. Imagine generating an entire suite of these calculations with just one click!
This Quick Win demonstrates how Tabular Editor's C# scripting can automatically generate time intelligence measures for your Power BI models, saving hours of development time while ensuring consistency across your reports.
1. Open Tabular Editor
First, you need to have Tabular Editor installed as an external tool for Power BI Desktop. If you haven't done this yet, download Tabular Editor from:
https://www.sqlbi.com/tools/tabular-editor/
and follow the installation instructions.
Once installed:
Open your Power BI Desktop file
Go to the External Tools tab in the ribbon
Click on Tabular Editor
2. Create and Write Your C# Script
Now it's time to create the script that will generate your time intelligence measures:
Right-click on your selection and choose Create New Script
In the script editor, enter the following C# code:
// Creates time intelligence measures for every selected measure:
foreach(var m in Selected.Measures) {
// Previous-Year:
var Y1 = m.Table.AddMeasure(
m.Name + " Y-1", // Name
"CALCULATE("+ m.DaxObjectName +",DATEADD('Date'[Date],-1,YEAR))", // DAX expression
m.DisplayFolder = m.DaxObjectName.Replace("[", "").Replace("]", "") // Display Folder
);
Y1.FormatString = m.FormatString;
Y1.FormatDax();
}
3. Run the Script for Selected Measures
Execute your script by:
Clicking the Play button in the script editor
Or pressing F5 on your keyboard
Instantly, you'll see new measures appear in your model - one for each measure you selected, with the " Y-1" suffix.
4. Add More Calculations
YoY (%) - Year over Year (%)
// Creates time intelligence measures for every selected measure:
foreach(var m in Selected.Measures) {
// Year-over-Year %:
var YoY = m.Table.AddMeasure(
m.Name + " YoY (%)", // Name
"VAR LY = IF(SELECTEDVALUE('Date'[Year]) = YEAR(TODAY()),CALCULATE("+ m.DaxObjectName +",SAMEPERIODLASTYEAR('Date'[Date]),'Date'[Day] <= DATEDIFF ( DATE ( YEAR(TODAY()), 1, 1 ), TODAY (), DAY )),CALCULATE("+ m.DaxObjectName +",SAMEPERIODLASTYEAR('Date'[Date])))RETURN DIVIDE(( "+ m.DaxObjectName +" - LY),LY)", // DAX expression
m.DisplayFolder = m.DaxObjectName.Replace("[", "").Replace("]", "")// Display Folder
);
YoY.FormatString = "0.0 %"; // Set format string as percentage
YoY.FormatDax();
}
YoY ABS - Year over Year ABS
// Creates time intelligence measures for every selected measure:
foreach(var m in Selected.Measures) {
// Year-over-Year %:
var YoY = m.Table.AddMeasure(
m.Name + " YoY ABS", // Name
"VAR LY = IF(SELECTEDVALUE('Date'[Year]) = YEAR(TODAY()),CALCULATE("+ m.DaxObjectName +",SAMEPERIODLASTYEAR('Date'[Date]),'Date'[Day] <= DATEDIFF ( DATE ( YEAR(TODAY()), 1, 1 ), TODAY (), DAY )),CALCULATE("+ m.DaxObjectName +",SAMEPERIODLASTYEAR('Date'[Date])))RETURN "+ m.DaxObjectName +" - LY", // DAX expression
m.DisplayFolder = m.DaxObjectName.Replace("[", "").Replace("]", "")// Display Folder
);
YoY.FormatString = m.FormatString;
YoY.FormatDax();
}
Voilá!
Automatic measure generation with Tabular Editor is a game-changer for Power BI developers. This technique transforms what used to be hours of tedious DAX coding into seconds of automated work. Not only does it save time, but it also improves the quality and consistency of your semantic models.
Next time you need to create time intelligence measures, don't do it manually - leverage the power of Tabular Editor and C# scripting to work smarter, not harder.
