Solved: Calculate time in minutes between two dates - Page 2

Adamzzzz · ‎12-06-2016

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 · ‎12-06-2016

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

Lima - Peru

View solution in original post

Cavring · ‎12-12-2016

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:

Duplicated Date Time twice and extracted this into the Time and Hour columns
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)
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

View solution in original post

Calculate time in minutes between two dates

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly