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 generate a Test column with the values: 0,0.01,0.03,0.02,0, etc.
I have a table called Query1 that contains a measure called running index that calculates running index with:
1 running index = CALCULATE(Query1[adjusted_renewal_cost running total in renewal_year]) / calculate([Measure1], all(Query1))
Target year is a column in my Query1 table. I’m thinking I need to reference this column in some way, but I may be wrong.
1 Reply
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])returnif(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.
