Create Matrix column to show % difference between years

[antongg7]

I have a matrix in power BI Desktop like the following one (real data deleted due to confidentiality reasons): 

To build this table I used the following DAX: 

Valores P&L Cuenta Explotación =

VAR _conceptoNivel2 = max('P&G - Jerarquía'[orden nivel 2])

VAR _conceptoNivel1 = max('P&G - Jerarquía'[orden nivel 1])

var _conceptoDesc = max('P&G - Jerarquía'[orden descripción])

VAR _calc =

SWITCH(

    TRUE(),

    _conceptoNivel2=1, [Gastos Totales P&G],

    _conceptoNivel2=2, [Ingresos Totales P&G],

    _conceptoNivel2=3 && _conceptoNivel1= 11, [EBITDA],

    _conceptoNivel2=3 && _conceptoNivel1= 12, [Amortizaciones],

    _conceptoNivel2=3 && _conceptoNivel1= 13, [Beneficio Antes Impuestos]

)

RETURN IF(ISBLANK(_calc),0,_calc)

And for the columns I use "'DIM Calendar'[Year]".

Now I would need to ad a column in between each year that shows the % change vs the previous year. The new columns should look something like this for every row:  (ideally just for the Ventas (green) grouping, but if its for all of the rows its fine)

2024 | % vs Previous year | 2023 | % vs Previous year | 2022 |

------------------------------------------------------------------

1100 | +10%                     | 1000 | +20%                     | 800   | 

I need that when a grouping is closed or open it shows the % for the value showed in that specific groping. If I close the Ventas group, the % should be for the total Ventas value. If it is open it should be for every subgroup. 

Is there a way to do this? Thanks a lot! 

[Samarth_18 · Accepted solution]

Hi @antongg7 ,

You could try below measure:-

CurrentYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value])
    )
)

PreviousYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value]) - 1
    )
)

PercentChange = 
IF(
    ISBLANK([PreviousYearValue]),
    BLANK(),
    DIVIDE([CurrentYearValue] - [PreviousYearValue], [PreviousYearValue], 0)
)