Calculating Duration Between In / Out Times

[fetster]

Hi. I have a table that has the following data:

TicketID	Status	Time
201	In	09:55
202	In	10:02
203	In	10:23
204	In	10:35
201	Out	10:49
205	In	10:53
206	In	11:21
202	Out	11:23
203	Out	11:31
204	Out	11:44

I would like to calculate the duration each TicketID has been in the building (Difference between In / Out Time for every TicketID).

Any suggestions how this could be calculated please? 

[aj1973]

Hi @fetster 

Here you have the choice to use cards and to return 0 when the satus for a ticket is not Out yet

Don't forget to format the Time column and the card

[Jihwan_Kim · Accepted solution]

Hi, @fetster 

Please check the below picture and the sample pbix file's link down below.

duration measure =

VAR intime =

CALCULATE ( MAX ( 'Table'[Time] ), 'Table'[Status] = "In" )

VAR outtime =

CALCULATE ( MAX ( 'Table'[Time] ), 'Table'[Status] = "Out" )

VAR durations = outtime - intime

RETURN

IF ( ISFILTERED ( 'Table'[TicketID] ), durations )

https://www.dropbox.com/s/yqitt8pmu0na882/fetster.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM