Calculate time between actions choosed by the user

[KevSan]

Hi everybody,

I'm trying to calculate the time spent between actions in a warehouse. The goal is to let the end user choose the "beginning" action and the "finishing" action to evaluate. 

How can I do that ? 

In attachment, an "example" of raw data...

[KevSan]

I try again with this : 

I want my user to select 2 parameters : Start action and End Action -> These 2 parameters are coming from the same column 'Action' and are unique by 'OrderNo'. 

My goal is to calculate the time difference between those 2 actions. 

@amitchandak or @Greg_Deckler could you help me with this? I'm stuck with this and I feel like sh** 😄 

A big thank for your help and have a nice day.

[Greg_Deckler]

@KevSan Probably need to create 2 disconnected tables (no relationshisps) with your list of Actions. Use those for slicers. You can then use SELECTEDVALUE to get the selected actions and then do the duration calculation in your measure based on that.

[KevSan]

@Greg_Deckler thanks for your advice. Could you please help me and tell me more about how to do the measure with the selectedvalue function? 

[Greg_Deckler · Accepted solution]

@KevSan Assuming you have the 2 disconnected tables, ActionTable1 and ActionTable2 you could do something like this:

Measure =
  VAR __Action1 = SELECTEDVALUE('ActionTable1'[Action])
  VAR __Action2 = SELECTEDVALUE('ActionTable2'[Action])
  VAR __OrderNo = MAX('Table'[OrderNo])
  VAR __StartDate = MAXX(FILTER('Table',[OrderNo] = __OrderNo && [Action] = __Action1),[Date])
  VAR __StartTime = MAXX(FILTER('Table',[OrderNo] = __OrderNo && [Action] = __Action1),[Hour])
  VAR __EndDate = MAXX(FILTER('Table',[OrderNo] = __OrderNo && [Action] = __Action2),[Date])
  VAR __EndTime = MAXX(FILTER('Table',[OrderNo] = __OrderNo && [Action] = __Action2),[Hour])
  VAR __StartDateTime = DATEVALUE(__StartDate) + TIMEVALUE(__StartTime)
  VAR __EndDateTime = DATEVALUE(__EndDate) + TIMEVALUE(__EndTime)
RETURN
  (__EndDateTime - __StartDateTime) / (1/24/60)

Returns minutes.

[KevSan]

Thanks @Greg_Deckler . I need to test it but it seems nice.

Have a nice day !