Calculate time in minutes between two dates

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])

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