Measures – Dynamic Percent Change – Using DAX

How do I calculate percent change between the minimum and maximum year selected in DAX?

TL;DR. % Change is DIVIDE of MAX-year production over MIN-year production, else 0, then minus 1. Format it as Percentage. Only a new first or last year changes the number. Here, 2007 to 2013 is 19.15%, not a fixed 2014 over 2013.

How is % Change written on this page?

It is DIVIDE of two CALCULATE sums on 'Auto Production'[Production]. The numerator filters Year to MAX. The denominator filters Year to MIN. DIVIDE’s third argument is 0. Then subtract 1.

Why DIVIDE instead of a slash?

Building the Dynamic Percent Change Measure says a zero denominator would error. DIVIDE can return zero instead.

Which selections actually change the number?

Years 2013 and 2007 produce 19.15% (smallest 2007, largest 2013). A year between those ends does not change it. Selecting 2014 does, because the end year changed.

How is the measure formatted?

Change % Change from General to Percentage on the Modeling ribbon, under Formatting.

How is this different from the previous percent tutorial?

The previous tutorial locked 2014 and 2013 (Total 2014, Total 2013, then divide minus 1) on 'Global Production'. This page uses 'Auto Production' and divides MAX-year production by MIN-year production. It is not month-to-month, year-over-year, or CAGR.

Related: Measures – Calculating % Change, Measures – Dynamic CAGR Calculation in DAX, and Measures – Month to Month Percent Change.

This tutorial will produce a measure that will dynamically calculate a percent change every time an item is selected in a visual. The previous tutorial can be found here. In the previous tutorial we calculated the percent change between two time periods, 2014 and 2013. In practice it is not always desirable to force your measure to only look at two time periods. Rather it would be nice that your measure calculations change with changes in your selections on visuals. Thus, for this tutorial we will add some dynamic intelligence to the measures.

Loading the Data

First here is the data we will be using. This data is the same data source as used in the previous % change tutorial. To make things easy I’ll give you the M code used to generate this query. Name this query Auto Production.

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Automotive_industry")),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0, {"Change", "Source"}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Columns", {{"Production", Int64.Type}, {"Year", Int64.Type}}
    )
in
    #"Changed Type"

Note: The code shown above should be added as a blank query into the query editor. Add the code using the Advanced Editor. Another tutorial showing you how to add advanced editor code is here.

Once you’ve loaded the query called Auto Production, the Field list should look like the following:

Setting Up the Table Visual

Next add a Table with Production and Year. This will allow us to see the data we are working with. When you initially make the table the Year and Production columns are automatically summed, thus why there is one number under year and production.

Rather we want to see every year and the production values for each of those years. To change this view click on the triangle in the Values section of the Visualizations pane. This will reveal a list, in this list it shows that our numbers are aggregated by Sum - change this to Don’t Summarize.

Now we have a nice list of yearly production levels with a total production at the bottom of our table.

Building the Dynamic Percent Change Measure

Next we will build our measure using DAX to calculate the percent changes by year. Our Calculation for % change is the following:

% Change = ( New Value / Old Value ) - 1

Below is the DAX statement we use as our measure. Copy the below statement into a new measure.

% Change =
DIVIDE(
 CALCULATE(
 SUM('Auto Production'[Production]),
 FILTER('Auto Production','Auto Production'[Year]=MAX('Auto Production'[Year])
 )
 ),
 CALCULATE(
 SUM('Auto Production'[Production]),
 FILTER('Auto Production','Auto Production'[Year]=MIN('Auto Production'[Year])))
,0)
- 1

Note: We are using the DIVIDE function for division. This is important because if we run into a case where we have a denominator = 0 then an error is returned. Using DIVIDE allows us to return a zero instead of an error.

Creating the Visuals

Next add our newly created measure as a Card.

Change the % Change measure format from General to Percentage, do this on the Modeling ribbon under Formatting.

Next add a slicer for Year.

Testing the Dynamic Calculation

Now you can select different years and the % change will automatically change based on our selection. The % change will always select the smallest year’s production and the largest year’s production to calculate the % Change. By selecting the Year 2013 and 2007, the percent change is 19.15%. The smallest year is 2007 and the largest is 2013.

If we select a year between 2013 and 2007 the measure will not change.

The measure will only change when the starting and ending years are changed. By selecting the year 2014, the measure finally changes.

Pretty cool wouldn’t you say? Thanks for taking the time to walk through another tutorial with me.