Calculate percentage difference in year on year values

[riffraff55]

Newbiw here to Power BI and DAX. I am looking to calculate the percentage difference in year on year values and then writing the derived value in a new column.Ive written the following DAX formula but it keeps saying theres a syntax error so not sure what I am doing wrong. I would be grateful if anyone can point me in the right direction:

PercentageChange =
VAR PrevYrGrowth =
    CALCULATE(
        MAX('PProj'[P Growth]),
            'PProj'[CName] = EARLIER('PProj'[CName]) &&
            'PProj'[Year] = EARLIER('PProj'[Year]) - 1
        )
RETURN
IF(
    NOT(ISBLANK(PrevYrGrowth)),
    (('PProj'[P Growth] - PrevYrGrowth) / PrevYrGrowth) * 100,
    BLANK()
)

The CName column is a text coloumn representing a country name. the Year column is a number column representing a Year in the format yyyy
The P Growth column is a decimal colum rounded to 2 decimal places

[riffraff55]

just for my learning exprience, what would be the difference in creating it as a measure rather than as a column? is it just good practice or is the coding completely different?

[hnguy71]

Hi @riffraff55 ,

They're both DAX specific expressions and each of them have their advantages. Having it as a measure gives you more flexibility and can give you results based on visual level context at the aggregate level and does not take up memory, thus letting your model refresh faster.

But if you want to do specific row level calculations, then calculated column(s) may be better suited, but do note that on reresh, these columns are calculated which may slow down your model refresh.

[hnguy71 · Accepted solution]

Hi @riffraff55 

I would recommend building it as a measure instead of a calculated column, but if you still insist it being a column this should work:

PercentageChange = 

VAR _Year = [Year]

VAR _CY = 
CALCULATE(
    SUM(PProj[P Growth]), 
    FILTER(ALL(PProj), [CName] = EARLIER([CName]) && [Year] = _Year)
)


VAR _PY = 
CALCULATE(
    SUM(PProj[P Growth]), 
    FILTER(ALL(PProj), [CName] = EARLIER([CName]) && [Year] = _Year - 1)
) 

RETURN

DIVIDE(_CY - _PY, _PY) * 100