Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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])))
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |