Percentage change with blank years

[Anonymous]

I need a solution for percentage change  when there is a year without values. In the example below i need that the % change and the difference of percentage points in 2015 that compares with 2013 instead 2014.

My formulas:

Valor (YoY;%) = IFERROR(([OptConsumoAgua]-[OptConsumoAgua_N-1])/[OptConsumoAgua];BLANK())

Valor (YoY;p.p.) = IFERROR([OptConsumoAgua]-[OptConsumoAgua_N-1];BLANK())*100

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Not sure how you calculate percentage value for previous year. 

In my test, I added a calculated column to return YearNo in previous row.

Previous year =
CALCULATE (
    MAX ( 'Year-percetange'[Ano] ),
    FILTER (
        ALL ( 'Year-percetange' ),
        'Year-percetange'[Ano] < EARLIER ( 'Year-percetange'[Ano] )
    )
)

Then, the measure for [OptConsumoAgua_N-1] is similar to:

OptConsumoAgua_N-1 =
CALCULATE (
    [OptConsumoAgua],
    FILTER (
        ALL ( 'Year-percetange' ),
        'Year-percetange'[Ano] = MAX ( 'Year-percetange'[Previous year] )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

 OptConsAgua_N-1  Function gives me a wrong result (see table), maybe because filters?

Can you correct the function below?

OptConsAgua_N-1 = CALCULATE([OptConsAgua];FILTER(ALL('Optimização');'Optimização'[Ano]=max('Optimização'[Ano Anterior])))