Concurrent Available Time

[stribor45]

I have data that containts start time (datetime) agent become available and when end time (datetime). I also have duration how many minutes from start to end.  (decimal). Additionally this table also have date this occured (date)  which is connected to my Calendar table in many to one relationship.  

Since I am filtering the statuses in power query my table in report contains only "Available" statuses

Table1

.............................

StartTime

RepName

DurationOnline

Status

EndTime

CallDate

CalendarTable

-----------------

Date

I will be filtering this based on one day using slicer. I am always only interested in looking at data one day at the time. anyone has suggestions how to go about this?

Thanks

[burakkaragoz · Accepted solution]

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.

[v-sshirivolu]

Hi @stribor45 ,
Thanks for reaching out to Community Forum.

I tested this using a minute level repro and the results are correct. The table shows how availability overlaps at each minute. From 09:00 to around 09:14 only one interval is active, so the concurrent count stays at 1. From 09:15 onwards, multiple intervals overlap, so the count increases accordingly. The card visual returning Max Concurrent Available = 3 is expected, because at no single minute do more than three intervals overlap. This confirms the measure is calculating true concurrency minute by minute intersection of time ranges, not just counting rows or summing durations.

I've attached a  .PBIX below with the same logic and visuals, which you can use to validate or compare against your model.

Thank you.

[v-sshirivolu]

Hi @stribor45 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @stribor45 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[lbendlin]

The usual approach is to use minute level buckets and then use INTERSECT .

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...