cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculate diff in an cumulative values

Hallo all,

I have this table as below, and I am looking for a way to calculate a new column.

So here the values are accumulative for each ID, therefore I am looking for a way to calculate the difference between each value and the value before it for each ID.

I could use this formula, but it does not work to distinguish each ID that I have.

Column =
VAR NextIndex = Wirkarbeit[Index] - 1
RETURN
Wirkarbeit[Value]
- CALCULATE (
VALUES ( Wirkarbeit[Value] ),
FILTER ( ALL ( Wirkarbeit), Wirkarbeit[Index] = NextIndex )
)

1 ACCEPTED SOLUTION
Super User

@TatyDo , Try a new column like

new column =
var _max =maxx(filter(Table, [ID] = earlier([ID]) && [datetime] <earlier([Datetime])),[Datetime])
return
if(isblank(_max), [value] , [value] -maxx(filter(Table, [ID] = earlier([ID]) && [datetime] =_max),[value]))

Super User

@TatyDo , Try a new column like

new column =
var _max =maxx(filter(Table, [ID] = earlier([ID]) && [datetime] <earlier([Datetime])),[Datetime])
return
if(isblank(_max), [value] , [value] -maxx(filter(Table, [ID] = earlier([ID]) && [datetime] =_max),[value]))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.