cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Percentage change with blank years

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

1 ACCEPTED SOLUTION
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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])))

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors