Count In Running Calls

Question

Hello&nbsp;I have two tables, a Date Table and a Table with "Shows" with a Start and End Date/Time. I want to add a column on the Date Table that counts how many Shows are in running in the time periods on the Date Table.&nbsp;&nbsp;&nbsp;1. ShowsShow&nbsp; &nbsp; UTC_START_TIME&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UTC_END_TIME&nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 04:30:00&nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 06:20:00&nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 04:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 06:35:00&nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 04:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 07:55:00&nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 06:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 09:30:00&nbsp;2. Date/Time Table&nbsp;&nbsp;DateTime &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End Of Period Date/Time &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (New Column) InRunning01/01/2019 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 00:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 (Show 1 &amp; 2 are In Running)01/01/2019 01:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 01:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;(Show 1 &amp; 2 are In Running)01/01/2019 02:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 02:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;(Show 1 &amp; 2 are In Running)01/01/2019 03:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 03:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;(Show 1 &amp; 2 are In Running)01/01/2019 04:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 04:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;(Show 3 &amp; 4 Started and Show 1 &amp; 2 are In Running)01/01/2019 05:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 05:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;(Show 2, 3 &amp; 4 are&nbsp; In Running and Show 1 has Ended)01/01/2019 06:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 06:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;4 (Show 2, 3 &amp; 4 are&nbsp; In Running and Show 5 has Started)01/01/2019 07:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 07:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 (Show 4 &amp; 5 are In Running and Show 2 &amp; 3 have Ended)&nbsp;01/01/2019 08:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 08:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 (Show 5 is In Running and Show 4 has Ended)01/01/2019 09:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 09:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 (Show 5 is In Running)01/01/2019 10:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019 10:59:59&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 (No Shows are Running)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I have used the formula below, which after checking the numbers, is incorrect. It seems to only take the previous column's values into consideration.&nbsp;&nbsp;&nbsp;InRunning = COUNTROWS (FILTER ('Shows',OR ('Shows'[UTC_START_TIME]&gt;= EARLIER ( [DateTime] )&amp;&amp; 'Shows'[UTC_START_TIME] &lt;= EARLIER ( [End Of Period Date/Time] ),'Shows'[UTC_END_TIME] &gt;= EARLIER ( [DateTime] )&amp;&amp; 'Shows'[UTC_END_TIME] &lt;= EARLIER ( 'DATE'[End Of Period Date/Time] ))))&nbsp;&nbsp;I appreciate your help here&nbsp;ThanksJoe

alb · Accepted Answer

Hi Anonymous&nbsp;
Try this for a new calculated column in your DateTable:
&nbsp;
&nbsp;
InRunning =
COUNTROWS (
    FILTER (
        'Shows',
        'Shows'[UTC_END_TIME] &gt; 'DATE'[DateTime]
            &amp;&amp; 'Shows'[UTC_START_TIME] &lt; 'DATE'[End Of Period Date/Time]
    )
)
&nbsp;

Forum Discussion

Count In Running Calls

2 Replies

Related content

Running Count in DAX Sorted by Date

Counting nulls

Running Maximum

Running sum

running totals count