Forum Discussion

Abi_W_981's avatar
Abi_W_981
Frequent Visitor
3 years ago

Calculating Difference from Previous Row

Hi, we have a table of data and results for each half term for a unique ID.   Example table below - what I want to be able to find is the half termly changes in grade value between half terms (excl...
Need Help
HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
3 years ago

You can add an Index column in Power Query (via the interface) and then

 

A calculated column in DAX:

 

ColumnQ = 
var _ID = TableA[Unique ID]
var _Index = TableA[Index]
RETURN
IF (TableA[Half Term] <> "Autumn 1",
TableA[Grade Value] - 
CALCULATE(SUM(TableA[Grade Value]), 
  FILTER(TableA, TableA[Unique ID] = _ID && TableA[Index] = _Index - 1)),
0)

 

 

 

Abi_W_981's avatar
Abi_W_981
Frequent Visitor
3 years ago

Hi, thank you for this

Just to check is this formula relying on Autumn 1 always being first for each individual Unique ID?

 

Something I have to mention is that for these results, the "first" result may not necessarily always be Autumn 1 and so the first result could be Autumn 2 or Spring 1 for example.

 

Would this affect the formula above?