Concurrent Available Time

Hi stribor45 ,

It looks like you are trying to solve the classic "Events in Progress" problem. As @lbendlin suggested, the standard approach is to use "minute level buckets" and check for overlaps. @v-sshirivolu also confirmed that calculating the "minute by minute intersection" is the correct logic to verify concurrency.

Since you are analyzing one day at a time, here is the step-by-step implementation of that strategy:

Step 1: Create a Disconnected Time Table

You need a separate table to act as your X-axis (00:00 to 23:59). This table should not be connected to your main data model.

Go to the Modeling tab > New Table and paste this DAX:

TimeTable = 
GENERATESERIES(
    TIME(0, 0, 0), 
    TIME(23, 59, 0), 
    TIME(0, 1, 0)
)

• Rename the column created to [Time].

• Change the data type to Time.

Step 2: The Concurrency Measure

Now, write a measure that iterates through each minute of your new TimeTable and counts how many agents were "online" during that specific minute.

Concurrent Agents = 
VAR CurrentTime = MAX('TimeTable'[Time])
VAR SelectedDate = SELECTEDVALUE('CalendarTable'[Date])
VAR CurrentDateTime = SelectedDate + CurrentTime

RETURN
    CALCULATE(
        COUNTROWS('Table1'),
        -- Logic: Agent started before this minute AND ended after this minute
        'Table1'[StartTime] <= CurrentDateTime,
        'Table1'[EndTime] >= CurrentDateTime
    )

Step 3: Visualize It

1. Add a Line Chart to your report.

2. X-Axis: Put 'TimeTable'[Time].

3. Y-Axis: Put the [Concurrent Agents] measure.

4. Slicer: Ensure you have your 'CalendarTable'[Date] slicer selected for a single day.

Why this works: Since TimeTable is disconnected, it doesn't filter your data automatically. The measure manually grabs the "Current Minute" from the chart axis, combines it with the "Selected Date" from your slicer, and counts how many rows in Table1 overlap with that specific moment in time.

Would you like me to explain how to optimize this measure if you have a very large dataset (millions of rows)?

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.