Add custom column that aggregate sum/subtraction column based on values from other columns

Hi andreagr ,

Here's the possible solution.

In Power Query, you need to add three index columns for this. They are created in Power Query.

Create a measure. 21.67 and 5.83 are correct.

HRS INT = var _previousID=IF(MAX('Table'[Intervention])=1,CALCULATE(MAX('Table'[ID]),FILTER(ALL('Table'),[Index.2]=MAX('Table'[Index.2])-1&&[Intervention]=1)))
var _currentstamp=MAX('Table'[Timestamp])
var _startsameID=MAX('Table'[Start Time])
var _previousIDend=CALCULATE(MAX('Table'[End Time]),FILTER(ALL('Table'),[Index]=MAX('Table'[Index])-1))
var _previousstamp=CALCULATE(MAX('Table'[Timestamp]),FILTER(ALL('Table'),[Index]=MAX('Table'[Index])-1&&[Intervention]=1))
return IF(_previousID<>MAX('Table'[ID])&&MAX('Table'[Intervention])=1,DATEDIFF(_startsameID,_currentstamp,MINUTE)/60+DATEDIFF(_previousstamp,_previousIDend,MINUTE)/60,IF(_previousID=MAX('Table'[ID]),DATEDIFF(_previousstamp,_currentstamp,MINUTE)/60))

For the three index columns, you can download my attachment to go to Power Query to see how to create it. For 4, not 31 and other desired outcomes, I need more details.   

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.