PowerBi subtracting two cells in different rows with condition

user_8458172 0
Viewed 1199

I am wondering if something that i would like to achieve is possible, please look at the picture and read description below:

enter image description here

I would like to add a column to the right, where if a cell table[ActionType] = "TERMINATING", it calculates a difference between timestamps (timestamp for TERMINATING - timestamp for STARTING in below row). If the result is positive (>0) then store it in a column in a corresponding row (eg next to timestapm for terminating), if result is negative don't store it. And all of that applied to whole table.

I tried conditional column and i guess it cannot be done with this or at least I couldn't make it.

Will be very thankful for responses and tips!

dax powerbi
1 Answers

Pre-requisite :- Add an Index Column using the query editor. Make sure they are in the next row to each other.

It is advisable to keep TimeStamp column as a DateTime Column itself.

So, if you can change your TimeStamp column to a DateTime column then try this :-

Difference = 
   Var Get_Action_Type = Table1[ActionType]
   Var required_Index_1 = Table1[Index] + 1
   Var required_Index = IF(required_Index_1 > MAX(Table1[Index]),required_Index_1-1, required_Index_1)
   Var Current_Action_TimeStamp = Table1[TimeStamp]
   Var next_Action_TimeStamp = CALCULATE(MAX(Table1[TimeStamp]),FILTER(Table1, Table1[Index] = required_Index))
   Var pre_result = IF(Get_Action_Type = "TERMINATING", DATEDIFF(Current_Action_TimeStamp, next_Action_TimeStamp,SECOND), BLANK())
   Var result = IF(pre_result > 0, pre_result, BLANK())
return result

And if you cannot change it to a Date Time, then try this calculated column, 

Difference_2 = 
   Var Get_Action_Type = Table1[ActionType]
   Var required_Index_1 = Table1[Index] + 1
   Var required_Index = IF(required_Index_1 > MAX(Table1[Index]),required_Index_1-1, required_Index_1)
   Var Current_Action_TimeStamp = Table1[Time_Stamp_Number]
   Var next_Action_TimeStamp = CALCULATE(MAX(Table1[Time_Stamp_Number]),FILTER(Table1, Table1[Index] = required_Index))
   Var pre_result = IF(Get_Action_Type = "TERMINATING", next_Action_TimeStamp - Current_Action_TimeStamp, BLANK())
   Var result = IF(pre_result > 0, pre_result, BLANK())
return result

The Output looks as below :-

enter image description here

Kindly accept the answer if it helps and do let me know, how it works for you.

user_76381740
Related