Calculated column looking up previous value

[FrontierGroup]

Hi

I have the following data

I would like to create a column subtracting the previous value, so I get a usage per line

Appreciate any help

Thanks

Steve

[Anonymous · Accepted solution]

Hi @FrontierGroup ,

follow these steps.

1. Add the Index column like this.

(Edit queries-->Add column--> Index Column--> from 1)

2. You will get the Output something like this: (close and Apply after these steps)

3. Now add this calculated Column like this and add the formula:

Difference = 
 var curIndex = 'Table'[Index]
  var curCI = 'Table'[CI]
  var OLDIndex = 'Table'[Index]-1
  var oldVal = CALCULATE(
    FIRSTNONBLANK('Table'[CI],1),
    FILTER('Table', 
    'Table'[Index]=OLDIndex))
  return IF(CONTAINS('Table','Table'[Index],OLDIndex), curCI-oldVal, 0)

My output based on your sample:

Let me know if this works.

Thanks,

Tejaswi

[FrontierGroup]

Hi Tejaswi

Thank you for your quick response and the solution you detailed works perfectly!

Thanks for your help

Steve