Calculate Prior Year Change

This article provides the steps for calculating Prior Year Change in Power BI reports. 

Detailed Information

You can calculate the change from the prior year using DAX. 

Example:

You have data for enrollment by term, and you want to show the percent change from the prior year.                                  

Steps:

1. Data model: You need a time dimension, and the dimension has a field that is calculable. This model has a "Term" dimension, and STRM is joining to the Census table (fact), and the STRM field can be used for the calculation. The "Census" table has a headcount field, and the headcount can be summed to get the total headcount.
   
    
2. Create measures:
   Four new measures will be created.

   1. headcount_current: calculate headcount for the current term.
      There will be two variables in this DAX. (The variable line starts with "VAR" and the next word is the variable name.) The first variable is "currentTerm" and it picks the most recent term selected for the visual. The second variable. output will be the final result you will get from this measure. It calculates the total headcount based on the first variable (currentTerm). Output is the same as the visual started with.

      headcount_current = VAR currentTerm = MAX ( Term[STRM] ) VAR output = CALCULATE ( SUM(Census[HEADCOUNT]), 
      FILTER ( ALL ( Term ), Term[STRM] = currentTerm) ) RETURN output

   2. headcount_py: headcount for the prior year. The difference between the current term (STRM) to the prior year's term is 100. If you want to use it for a 3-year change, it would be 300. The measure created in the previous step (headcount_current) was used to calculate.

      headcount_py =
      VAR currentTerm =
      	MAX ( Term[STRM] )
      VAR output =
      	CALCULATE (
      	SUM(Census[HEADCOUNT]),
      	FILTER ( ALL ( Term ), Term[STRM] = currentTerm -100)
      	)
      RETURN
      	output

      Output now shows the prior year's headcount. The STRM field was added to help visualize the calculation.

      
       

   3. headcount_py_diff: calculate the difference between the current headcount and the prior headcount. Additional DAX for dealing with blank values is added here.

      headcount_py_diff = IF(NOT(ISBLANK([headcount_PY])), [headcount_current]-[headcount_PY], BLANK())

      Output:

      

   4. headcount_py_%change: calculate the percent of change from the prior year. You may also need to change the formatting of the field to % and change the decimal point.

      headcount_py_%change = DIVIDE([headcount_py_diff], [headcount_current])

      Output:

      
3. Clean up visual
   Adding each measures to the visual was helpful for validating data, however, some fields are not necessary to show on the report. Remove the unwanted fields and rename the header. Final output became like this:

Additional Notes:

The steps above show each measure one by one; however, you can create the percent change within one measure. DAX can handle many variables (VAR).

You can create measures either way. Both ways have advantages.

headcount % change = 
VAR currentTerm =
    MAX ( Term[STRM] )
VAR headcount_current =
    CALCULATE (
        SUM ( Census[HEADCOUNT] ),
        FILTER ( ALL ( Term ), Term[STRM] = currentTerm )
    )
VAR headcount_py =
    CALCULATE (
        SUM ( Census[HEADCOUNT] ),
        FILTER ( ALL ( Term ), Term[STRM] = currentTerm - 100 )
    )
VAR headcount_py_diff =
    IF (
        NOT ( ISBLANK ( headcount_py ) ),
        headcount_current-headcount_py,
        BLANK ()
    )
RETURN 
    DIVIDE ( headcount_py_diff, headcount_current )

This calculation will work when the term is not in the same visual (e.g. card), based on the max term selected for the page.