Calculate time in minutes between two dates

[Adamzzzz]

Hello All, 

     I have data that shows the when a rep enters a mall (In) and leaves a mall (Out) I would like to be able to calculate how long the rep spends within a mall in minutes. 

The data looks like this 

Rep	Location	Status	Date Time
Rep 1	Massey Mall	In	2016/11/1 8:20
Rep 2	Crown Mall	In	2016/11/1 8:30
Rep 3	Pacific Mall	In	2016/11/1 8:40
Rep 2	Crown Mall	Out	2016/11/1 10:20
Rep 1	Massey Mall	Out	2016/11/1 11:00
Rep 3	Pacific Mall	Out	2016/11/1 12:00
Rep 2	Crown Mall	In	2016/11/2 8:00
Rep 1	Massey Mall	In	2016/11/2 8:20
Rep 3	Pacific Mall	In	2016/11/2 9:30
Rep 1	Massey Mall	Out	2016/11/2 10:00
Rep 3	Pacific Mall	Out	2016/11/2 10:00
Rep 2	Crown Mall	Out	2016/11/2 12:00

The output I am after is something like this on the out status

Rep	Location	Status	Date Time	Time Spent In Mall (Minutes)
Rep 1	Massey Mall	In	2016/11/1 8:20
Rep 2	Crown Mall	In	2016/11/1 8:30
Rep 3	Pacific Mall	In	2016/11/1 8:40
Rep 2	Crown Mall	Out	2016/11/1 10:20	110
Rep 1	Massey Mall	Out	2016/11/1 11:00	160
Rep 3	Pacific Mall	Out	2016/11/1 12:00	200
Rep 2	Crown Mall	In	2016/11/2 8:00
Rep 1	Massey Mall	In	2016/11/2 8:20
Rep 3	Pacific Mall	In	2016/11/2 9:30
Rep 1	Massey Mall	Out	2016/11/2 10:00	100
Rep 3	Pacific Mall	Out	2016/11/2 10:00	30
Rep 2	Crown Mall	Out	2016/11/2 12:00	240

I am not sure if a calculated column is the best solution and have no idea where to start. Any help would be appreciated. 

Thank you,
Adam

[Vvelarde · Accepted solution]

@Adamzzzz

Hi, please follow this and try with your data:

1: Create two columns Date and Hour

Date = Table1[Date Time].[Date]

Hour = TIME(HOUR(Table1[Date Time]);MINUTE(Table1[Date Time]);SECOND(Table1[Date Time]))

2. Create a measure to calculate the time spent in the mall

TimeSpent =
IF (
    AND ( HASONEVALUE ( Table1[Status] ), VALUES ( Table1[Status] ) = "OUT" ),
    CALCULATE (
        MAX ( Table1[Hora] ),
        ALLEXCEPT ( Table1, Table1[Rep], Table1[Location], Table1[Date] )
    )
        - CALCULATE (
            MIN ( Table1[Hora] ),
            ALLEXCEPT ( Table1, Table1[Rep], Table1[Location], Table1[Date] )
        )
)

2. Create a measure to convert in minutes 

TimeInMinutes = HOUR([TimeSpent])*60+MINUTE([TimeSpent])

[meguefa]

Boa tarde...
Estou montando um BI com média de permanência na fábrica. Quero medir o tempo que veículos ficam para descarregar.
Já montei toda a estrutura mas estou com dificuldade na configuração do eixo Y. Não consigo configurar para mostrar hh:mm.
Teria alguma dica para resolver?

[Cavring]

Hi @Adamzzzz

I was playing around with your data, but I didn't solve it, maybe this can lead you in the right direction though.

I did this in the query:

• Split dat and time
• Duplicated time twice
• transformed the first duplication to hours and the second to minutes
• Added a custom collumn = 60 x Hour + Minutes

It might not be the smoothes way but I find numbers easier to work with than time.

Instead of measures, try playing around with "New collumns". I had luck with that earlier.

Good luck,

Espen

[Vvelarde · Accepted solution]

@Adamzzzz

Hi, please follow this and try with your data:

1: Create two columns Date and Hour

Date = Table1[Date Time].[Date]

Hour = TIME(HOUR(Table1[Date Time]);MINUTE(Table1[Date Time]);SECOND(Table1[Date Time]))

2. Create a measure to calculate the time spent in the mall

TimeSpent =
IF (
    AND ( HASONEVALUE ( Table1[Status] ), VALUES ( Table1[Status] ) = "OUT" ),
    CALCULATE (
        MAX ( Table1[Hora] ),
        ALLEXCEPT ( Table1, Table1[Rep], Table1[Location], Table1[Date] )
    )
        - CALCULATE (
            MIN ( Table1[Hora] ),
            ALLEXCEPT ( Table1, Table1[Rep], Table1[Location], Table1[Date] )
        )
)

2. Create a measure to convert in minutes 

TimeInMinutes = HOUR([TimeSpent])*60+MINUTE([TimeSpent])

[Adamzzzz]

@Vvelarde Hello Victor, thank you for the response. I have tried the above measure and triple checking the correct spelling of the measure (making sure it is pointing to the correct columns). I recieve the following error- 

Calculation error in meaure. A table of multiple values was supplied where a single value was expected.

As a calculated column I only recieve blank results. I am unsure what I am doing incorrectly? any help would be appreciated. 

[Vvelarde]

@Adamzzzz

Post your measure to check and validate

[Adamzzzz]

https://www.dropbox.com/s/gedunf62y67biy2/Example.pbix?dl=0

@Vvelarde Thank you!

[Adamzzzz]

Any help would be appreciated. I cant figure this out 

[Cavring · Accepted solution]

Hi @Adamzzzz

I've had a look at what you have done and tweaked it a bit. It should be working now 🙂

This is the result:

This is what I've done in the query:

1. Duplicated Date Time twice and extracted this into the Time and Hour columns
2. Made a custom column , minutes (8x60 +20 = 500) to avoid having to work with the time format (I don't know if this is necessarry, but you can play around with both)
3. Duplicated Date and tranformed it into text for the Date - text column (again not sure if this i necessary but I didn't manage to get the same result without it)

I have made a new measure:

Time spent 2 =
 CALCULATE(max(Table2[Minutes]);ALLEXCEPT(Table2;Table2[Rep];Table2[Location];Table2[Date - text]))
-CALCULATE(min(Table2[Minutes]);ALLEXCEPT(Table2;Table2[Rep];Table2[Location];Table2[Date - text]))

Note:

• I have not added the HASONEVALUE, but that could easily be done.
• I was not able to reproduce the same result, using any of the columns formated as date ('Date time' or 'Date').
• You should als convert the 'Date Time' column into text and use that instead in the ALLEXCEPT in case a rep enters the mall twice in one day. 

Here's the file

Hope it works for you,

Espen

[Adamzzzz]

Thank you Espen works great. Thank you everyone for your help!

[v-ljerr-msft]

Hi @Adamzzzz,

The measures provided by Vvelarde should work. Could you post the measure you're using? It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards