Calculate total time someone spent playing a game

[sknnyftn]

I am trying to calculate the total time someone has spent playing a particular poker game. The problem I have is that they may be playing multiple games at the same time, or just one, and I have a simple table with chronological entry and exit times as shown below and in the pbx file. In cases where multiples are going at the same time, I don't have a preference on first in first out or last in first out methodology.

I have tried splitting the data out into seperate entry and exit tables using CALCULATETABLE, attempted to add an index column to both with ROWNUMBER, and develop a measure to using DATEDIFF between the two tables, with no luck. I am at a loss on how to get this done at all and would really appreciate any help.

Game Data.pbx 

Date/Time	Game	Type
12/07/24 6:14 PM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 3:36 PM	Texas Hold'em 0.25/0.5	Game Enter
12/07/24 12:28 PM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 10:56 AM	Texas Hold'em 0.25/0.5	Game Enter
12/07/24 12:22 AM	Texas Hold'em 0.25/0.5	Game Exit
12/07/24 12:21 AM	Texas Hold'em 0.25/0.5	Game Exit
12/06/24 9:07 PM	Texas Hold'em 0.25/0.5	Game Enter
12/06/24 7:05 PM	Texas Hold'em 0.25/0.5	Game Enter
12/06/24 1:02 PM	Texas Hold'em 0.25/0.5	Game Exit
12/06/24 11:32 AM	Texas Hold'em 0.25/0.5	Game Enter

[OwenAuger · Accepted solution]

Thanks for that @sknnyftn  🙂

I have uploaded a PBIX with one possible solution.

• I added a Date table. This table is disconnected from Data, but the measure created below takes into account the min/max Date filtered and determines the Game Duration intersecting that date range.
• The Game Duration measures are shown below:

Game Duration (days) = 
VAR MinDateTime = MIN ( 'Date'[Date] )
VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate
VAR Result =
    SUMX (
        Data,
        VAR CurrentType = Data[Type]
        VAR Multiplier = 
            SWITCH (
                CurrentType,
                "Game Enter", 1,
                "Game Exit", -1
            )
        VAR EffectiveDateTime =
            MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime )
        VAR Contribution =
            ( MaxDateTime - EffectiveDateTime ) * Multiplier
        RETURN
            Contribution
    )
RETURN
    Result

Game Duration (hours) = 
[Game Duration (days)] * 24

Game Duration (minutes) = 
[Game Duration (days)] * ( 24 * 60 )

This appears to give the correct result based on your sample data. Does it work as expected at your end?

Regards

[abuislam]

To calculate the total time someone spent playing a game with overlapping sessions, you can try sorting the data by date/time and calculating the time difference between entry and exit using DATEDIFF. You can sum these differences, keeping in mind overlapping sessions by ensuring you account for any concurrent games. If you're using Power BI, the EARLIER function might help with comparing rows for accurate calculation.

By the way, if you're looking for a fun game to relax and unwind, Car Parking Multiplayer is a great option. You can find more details about it at https://modcarparking.com/.

[OwenAuger]

Hi @sknnyftn 

Just confirming the exact requirements before suggesting a solution:

1. Do you want to calculate the total time for which at least one game is "active"?
   For example, two games active simultaneously for 5 minutes would give a result of 5 minutes?
2. Alternatively, do you want to calculate the total time for each game separately and sum?
   For example, two games active simultaneously for 5 minutes would give a result of 10 minutes?

Here's a plot of "Active Games" based on your sample data.

Active Games = # games entered so far less # games exited so far.

1. Option 1 above would measure the area under the minimum of the blue line and dotted red line.
2. Option 2 above would measure the area under the blue line.

[sknnyftn]

Is there a straightforward way to switch between  options 1 and 2?

[OwenAuger · Accepted solution]

@sknnyftn 

Excellent question!

I gave this some further thought, and we can write the measures differently in a way that makes switching between methods more straightforward.

I've attached an updated PBIX.

The idea is:

1. Construct a list of distinct Date/Time values (Entry & Exit).
2. For each Date/Time
   1. Determine the "next" Date/Time (I used the OFFSET function for this).
   2. Compute the number of "active games" (cumulative Entries less Exits).
3. For each Date/Time, multiply the time interval (adjusted to be within the filtered date range) by either:
   1. Option 1: 1 if there is at least one active game; or
   2. Option 2: The number of active games.

Below are the measures involved.

In the two final Game Duration measures, the only difference is the CountOverlaps variable.

TRUE corresponds to Option 2 and FALSE corresponds to Option 1.

Entry Count = 
CALCULATE (
    COUNTROWS ( Data ),
    KEEPFILTERS ( Data[Type] = "Game Enter" )
)

Exit Count = 
CALCULATE (
    COUNTROWS ( Data ),
    KEEPFILTERS ( Data[Type] = "Game Exit" )
)

Entry Count Cumulative = 
VAR MaxDateTime = MAX ( Data[Date/Time] )
VAR Result =
    CALCULATE (
        [Entry Count],
        Data[Date/Time] <= MaxDateTime
    )
RETURN
    Result

Exit Count Cumulative = 
VAR MaxDateTime = MAX ( Data[Date/Time] )
VAR Result =
    CALCULATE (
        [Exit Count],
        Data[Date/Time] <= MaxDateTime
    )
RETURN
    Result

Active Games = 
[Entry Count Cumulative] - [Exit Count Cumulative]

Game Duration (days) = 
-- Option 2
VAR CountOverlaps = TRUE
VAR MinDateTime = MIN ( 'Date'[Date] )
VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate

VAR DateTimes = VALUES ( Data[Date/Time] )
VAR Result =
    SUMX (
        DateTimes,
        VAR NextDateTime =
            OFFSET ( 1, DateTimes, ORDERBY ( Data[Date/Time] ) )
        VAR NextDateTimeNoBlank = COALESCE ( NextDateTime, MaxDateTime ) -- convert blank NextDateTime to MaxDateTime
        VAR EffectiveDateTimeStart =
            MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime )
        VAR EffectiveDateTimeEnd =
            MIN ( MAX ( NextDateTimeNoBlank, MinDateTime ), MaxDateTime )
        VAR ActiveGames = [Active Games]
        VAR Multiplier =
            IF ( CountOverlaps, ActiveGames, ActiveGames >= 1 )
        VAR Contribution =
            ( EffectiveDateTimeEnd - EffectiveDateTimeStart ) * Multiplier
        RETURN
            Contribution
    )
RETURN
    Result

Game Duration non-duplicated (days) = 
-- Option 1
VAR CountOverlaps = FALSE
VAR MinDateTime = MIN ( 'Date'[Date] )
VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate

VAR DateTimes = VALUES ( Data[Date/Time] )
VAR Result =
    SUMX (
        DateTimes,
        VAR NextDateTime =
            OFFSET ( 1, DateTimes, ORDERBY ( Data[Date/Time] ) )
        VAR NextDateTimeNoBlank = COALESCE ( NextDateTime, MaxDateTime ) -- convert blank NextDateTime to MaxDateTime
        VAR EffectiveDateTimeStart =
            MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime )
        VAR EffectiveDateTimeEnd =
            MIN ( MAX ( NextDateTimeNoBlank, MinDateTime ), MaxDateTime )
        VAR ActiveGames = [Active Games]
        VAR Multiplier =
            IF ( CountOverlaps, ActiveGames, ActiveGames >= 1 )
        VAR Contribution =
            ( EffectiveDateTimeEnd - EffectiveDateTimeStart ) * Multiplier
        RETURN
            Contribution
    )
RETURN
    Result

Does this work for you?

This likely requires some testing to confirm performance is acceptable.

Regards

[sknnyftn]

Sorry I got tied up on a different project and didn't respond sooner but this works perfectly.

[sknnyftn]

Owen, I am looking to accomplish option 2 and find area under the blue line.

[OwenAuger · Accepted solution]

Thanks for that @sknnyftn  🙂

I have uploaded a PBIX with one possible solution.

• I added a Date table. This table is disconnected from Data, but the measure created below takes into account the min/max Date filtered and determines the Game Duration intersecting that date range.
• The Game Duration measures are shown below:

Game Duration (days) = 
VAR MinDateTime = MIN ( 'Date'[Date] )
VAR MaxDateTime = MAX ( 'Date'[Date] ) + 1 -- Add 1 to represent exactly 0:00 at the end of MaxDate
VAR Result =
    SUMX (
        Data,
        VAR CurrentType = Data[Type]
        VAR Multiplier = 
            SWITCH (
                CurrentType,
                "Game Enter", 1,
                "Game Exit", -1
            )
        VAR EffectiveDateTime =
            MIN ( MAX ( Data[Date/Time], MinDateTime ), MaxDateTime )
        VAR Contribution =
            ( MaxDateTime - EffectiveDateTime ) * Multiplier
        RETURN
            Contribution
    )
RETURN
    Result

Game Duration (hours) = 
[Game Duration (days)] * 24

Game Duration (minutes) = 
[Game Duration (days)] * ( 24 * 60 )

This appears to give the correct result based on your sample data. Does it work as expected at your end?

Regards

[sknnyftn]

Owen, your solution appears to work perfectly. I tried with slightly different data and it tracked as expected. Thank you for the help.