DAX - PowerPivot measure to calculate ratio from prior year

Question

I have a dataset transaction records by year. Here's an example dataset for one account with 2 transactions each year from 2018 - 2020.

ID  YR  TRANS   VAL
A   2018    1   50
A   2018    2   100
A   2019    1   60
A   2019    2   75
A   2020    1   45
A   2020    2   80

I load it into the Excel data model and create a pivot table off of this model: ID in the rows, YR in the columns, and the sum of VAL for the values.

Like so (imagine this is a pivot table):

Sum of VAL  Column Labels       
Row Labels  2018    2019    2020
A            150     135     125

How can I create a measure to calculate the change from prior year? So the column for 2019 would be -10% and for 2020 would be -7.4%.

Something like this (again, imagine this is a pivot table):

            Column Labels                   
            Sum of VAL              CHNG_FROM_PRIOR     
Row Labels  2018    2019    2020    2018    2019    2020
A            150     135     125     N/a  -10.0%   -7.4%

msta42a msta42a · Accepted Answer · 2020-10-09T05:37:16

You can do something like that:

prior% =
VAR _cal =
CALCULATE (
    DIVIDE (
        [SUMVAL],
        CALCULATE (
            [SUMVAL],
            FILTER ( ALL ( 'Table'[YR] ), 'Table'[YR] = SELECTEDVALUE ( 'Table'[YR] ) - 1 )
        )
    )
)
RETURN
IF ( _cal = BLANK (), _cal, 1 - _cal )

DAX - PowerPivot measure to calculate ratio from prior year

1 Answers