Forum Discussion
Returning difference in row value
How would I go about subtracting previous row from current row and display those result in a new row? I’d like my first value to be 0 and I start my calculations in the 2nd row. I’m looking to gener...
If your target years are sequential, you can use this to refer to the previous row by creating a measure as follows:
test = var yr = SELECTEDVALUE(Query1[Target Year])
return
if(COUNTROWS(filter(all(Query1),[Target Year] = yr-1)) = 0, 0,
[running index] - CALCULATE([running index],all(Query1),Query1[Target Year] = yr-1))
The variable 'yr' captures the target year for the row we're looking at, then we test to see if we're in the first row and set to zero if we are, otherwise we calculate [running index] at yr-1 and subtract.
If your target years are not sequential, I recommend adding an index in the Power Query stage then using that index rather than [Target Year] in the measure above.
If you need to filter your table to non-sequential years (e.g. just filter to 2025 and 2028) then this won't work and you'll have to ask again if this is the case sorry.
The variable 'yr' captures the target year for the row we're looking at, then we test to see if we're in the first row and set to zero if we are, otherwise we calculate [running index] at yr-1 and subtract.
If your target years are not sequential, I recommend adding an index in the Power Query stage then using that index rather than [Target Year] in the measure above.
If you need to filter your table to non-sequential years (e.g. just filter to 2025 and 2028) then this won't work and you'll have to ask again if this is the case sorry.