Forum Discussion

andreagr's avatar
andreagr
Frequent Visitor
3 years ago

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

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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      andreagr
      Frequent Visitor
      3 years ago

      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!