Time spent in programm - measure

[Fistachpl]

Hello,

I have a table TProgram with 3 columns:

User, dateandtime, type

User is the name of the user

In this table column dateandtime is the dateand hour operation in format ds.mm.yyyy hh:mm:ss 

Type can be:

Login

Logout

And something other possibilities

Example data:

Adam, 25.01.2025 11:30:00, Login

Adam, 25.01.2025 12:00, Logout

Karol, 26.01.2025 09:00, Login

Karol, 26.01.2025 09:15, Offer made

Karol, 26.01.2025 09:30, Logout

Karol 26.01.2025 13:00 Login

Karol 26.01.2025 13:20, PhoneCall

And Now I want to calculate the time spent in the program on each day. 

The time spent should be calculated by subtracting dateandtime of Logout and Login. Please note that on some days someone can Login and Logout multiple times and therefore I need to have a sum of time spent. Second note is that someone can forget to Logout so the time spent in program should be calculated as last entry on that day (or last before next Login) minus time of Login. 

How can I do that? 

[Anonymous · Accepted solution]

Hi @Fistachpl 

First, create a calculated column for session end time:

EndTime = 
VAR CurrentUser = TProgram[User]
VAR CurrentDateTime = TProgram[dateandtime]
VAR CurrentDate = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime))
VAR NextLogout = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Logout" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR NextLogin = 
    CALCULATE(
        MIN(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            TProgram[type] = "Login" &&
            TProgram[dateandtime] > CurrentDateTime &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR LastEventOfDay = 
    CALCULATE(
        MAX(TProgram[dateandtime]),
        FILTER(
            ALL(TProgram),
            TProgram[User] = CurrentUser &&
            DATE(YEAR(TProgram[dateandtime]), MONTH(TProgram[dateandtime]), DAY(TProgram[dateandtime])) = CurrentDate
        )
    )
VAR MinNextEvent = 
    SWITCH(
        TRUE(),
        NOT ISBLANK(NextLogout) && NOT ISBLANK(NextLogin), MIN(NextLogout, NextLogin),
        NOT ISBLANK(NextLogout), NextLogout,
        NOT ISBLANK(NextLogin), NextLogin,
        BLANK()
    )
RETURN
IF(
    TProgram[type] = "Login",
    IF(
        NOT ISBLANK(MinNextEvent),
        MinNextEvent,
        LastEventOfDay
    ),
    BLANK()
)

Then create a calculated column for session duration:

Duration (Hours) = 
IF(
    TProgram[type] = "Login",
    DATEDIFF(TProgram[dateandtime], TProgram[EndTime], SECOND) / 3600,
    BLANK()
)

Then create a calculated column to sum the duration:

Total Time Spent (Hours) = 
SUMX(
    FILTER(TProgram, TProgram[type] = "Login"),
    TProgram[Duration (Hours)]
)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fistachpl]

Works like a charm. 
If I can have question. Why did You add columns? Is it not possible to do this via measure?