Relative Index to calculate difference between previous and actual row when FILTERED by several categories

DAX
1

Hello nice to meet you, I’ve been struggling with DAX for the last few hours, I hope you can help me. I have a single Table with Codigo_Casos, VAN DISTRITAL, Temperature Category, Concentrator Type Category, and other categories. The idea is to have a bar chart that shows the Cases (CODIGO_CASOS) and their NPV value, and that with several filters leaves cases according to filtered category, but I need to place The difference between each bar value of the chart .

It’s like calculating current value minus previous row, but when using an index as a reference, it fails when I use the filters since it’s a static index. To do this, I created 2 columns calculated using RANK to create index columns that will vary according to the “Concentrator Type” Filter and the measurement in that case works PERFECT.
Here is my measure

Diferencia_Categorias__ =
VAR CurrentValue = SELECTEDVALUE(‘Resumen’[VAN Distrital])
VAR TipoConcentradoraSeleccionada = SELECTEDVALUE(‘Resumen’[Tipo_Concnertadora])

VAR Diff =
IF (
MAX(‘Resumen’[Index_0]) = 0, // Verifica si ‘Index_0’ es igual a cero
0, // Si es así, establece la diferencia como cero
CurrentValue - CALCULATE(
SUM(‘Resumen’[VAN Distrital]),
FILTER(
ALL(‘Resumen’),
‘Resumen’[Index_2] = MAX(‘Resumen’[Index_0]) && // Category filter condition
‘Resumen’[Tipo_Concnertadora] = TipoConcentradoraSeleccionada // Additional filter condition
)
)
)
RETURN
IF(
Diff = VALUE(CurrentValue),
0,
Diff
)

The problem is that I have to apply more filters and the index columns fail. Create 2 measurements, filas_0 filas_2 that show what the indices should look like, with all the filters applied. Here’s the measure.
filas_2 = CALCULATE(COUNTROWS(‘Resumen’),FILTER(ALLSELECTED(‘Resumen’),‘Resumen’[Índice]<=max(‘Resumen’[Índice])))

What I haven’t been able to do is translate the formula that works (Diferencia_Categorias__) by replacing the calculated columns index_0 - index_2 with filas_0 _2 rows that are calculated MEASUREMENTS.

Please help me. this is driving me crazy lol
I attaced pbix and excel table.

BD_VAN_DRT_DCH.xlsx (29.5 KB)
Presentacion_Casos_VAN_2.pbix (798.5 KB)

2

I finally made what i wanted with this topic of GREG! Using TOPN is what i needed, not creating realtive indexes
Thank you so much!!

Value Previous Row (Discontinuous Dates) =
// DAX PATTERN NAME: Previous Row Value - Method 2 - Column
// NOTES: Use the TOPN construct with a column to return the previous value
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
MAX( ‘Values’[Value] ), – replace ‘Values’[Value] with the column containing the value of interest
TOPN(
1,
FILTER(
ALLSELECTED( ‘Values’ ), – replace [‘Values’] with the table containing your values
‘Values’[Value Date] < MAX( ‘Values’[Value Date] ) – replace ‘Values’[Value Date] with the column containing the value date
),
‘Values’[Value Date], – replace ‘Values’[Value Date] with the column containing the value date
DESC
)
)