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

[andreagr]

Hi! I am new to Power Bi and I'm struggling with something. Please see the screenshot below for reference. I am trying to create a new column "HRS INT" using DAX Measure or calculated column (I don't know what's the best approach). This new column will only have values where intervention=1.

if previous intervention had different ID than current: HRS INT = (Timestamp where current intervention=1 - start time of the same ID) + (End time of previous ID - Timestamp where previous intervention = 1) 

If previous intervention had the same ID as current: HRS INT= (Timestamp where current intervention=1 - Timestamp where previous intervention =1) of the same ID) 

If previous intervention happened before N amount (N>1) of IDs: (Timestamp where current intervention=1 - start time of the same ID) + ( End time of previous ID - Start Time of Previous ID) +( End Time of ID-2 - Start Time of ID-2) + .... + until I find an intervention

any help would be greatly appreciated!! please and thank you!

[Anonymous]

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.           

[andreagr]

Hi Stephen, 

This is great. Thank you. I have two questions/observations:

- Where did the 14.33 come from? That number should be (current timestamp - previous timestamp where intervention=1). Therefore, should be 1 hr. 

- The 4 is not correct because I want to also add up the hours of the previous ID(s) because they didn't have interventions. (current timestamp of intervention - start time)+ (previous end time - previous start time) + (previous-2 end time - previous-2 start time)= 31. I want to keep adding previous hours until I find an intervention. 

please let me know any ideas you have to solve this ! thank you so much!