DAX Comparison Measure

Modified on Sun, 26 Jul at 4:37 AM

Overview

QQube Comparison Measures are designed to reduce the need for customers to learn DAX before creating common financial comparisons in Power Pivot or Power BI.

It is not possible to anticipate every comparison a customer may need. However, each applicable data model includes enough examples that users can adapt an existing measure without spending hours researching DAX syntax.

Comparison Types

Simple Subtraction

Subtract one Calendar Specific Measure from another to show the amount of change between two periods.

Percentage Difference

Divide the calculated difference by the comparison-period value to show the relative percentage change.

Most QQube Comparison Measures are used in financial-related data models.

Calendar Growth Measures

Comparisons such as Year-Over-Year and Month-Over-Month are included with the Calendar Pattern Measures.

DAX Examples

Amount Difference

This measure compares Current Month-to-Date Actual with Previous Year Month-to-Date Actual and returns the amount difference.

Compare Actual - Current MTD vs Previous Year MTD = VAR ValuePlus = [Current Month-to-Date Actual] VAR ValueMinus = [Previous Year Month-to-Date Actual] VAR Result = IF ( NOT ISBLANK ( ValuePlus ) && NOT ISBLANK ( ValueMinus ), ValuePlus - ValueMinus ) RETURN Result

Blank-Value Protection

The calculation returns a result only when both comparison measures contain values. This prevents a misleading difference when either period is blank.

Percentage Difference

This measure divides the amount difference by Previous Year Month-to-Date Actual.

Compare Actual - Current MTD vs. Previous Year MTD % = DIVIDE ( [Compare Actual - Current MTD vs Previous Year MTD], [Previous Year Month-to-Date Actual] )

How the Measures Work Together

Measure	Result
Amount Difference	Current Month-to-Date Actual minus Previous Year Month-to-Date Actual.
Percentage Difference	The amount difference divided by Previous Year Month-to-Date Actual.

Expected Result

Comparison Measures provide ready-to-use amount and percentage differences between Calendar Specific Measures. Customers can also use these measures as templates for creating additional comparisons.