Count In Running Calls

[Anonymous]

Hello

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. 

1. Shows

Show    UTC_START_TIME                UTC_END_TIME

   1        01/01/2019 00:00:00          01/01/2019 04:30:00

   2        01/01/2019 00:00:00          01/01/2019 06:20:00

   3        01/01/2019 04:00:00          01/01/2019 06:35:00

   4        01/01/2019 04:00:00          01/01/2019 07:55:00

   5        01/01/2019 06:00:00          01/01/2019 09:30:00

2. Date/Time Table 

DateTime                            End Of Period Date/Time               (New Column) InRunning

01/01/2019 00:00:00          01/01/2019 00:59:59                      2 (Show 1 & 2 are In Running)

01/01/2019 01:00:00          01/01/2019 01:59:59                      2 (Show 1 & 2 are In Running)

01/01/2019 02:00:00          01/01/2019 02:59:59                      2 (Show 1 & 2 are In Running)

01/01/2019 03:00:00          01/01/2019 03:59:59                      2 (Show 1 & 2 are In Running)

01/01/2019 04:00:00          01/01/2019 04:59:59                      4 (Show 3 & 4 Started and Show 1 & 2 are In Running)

01/01/2019 05:00:00          01/01/2019 05:59:59                      3 (Show 2, 3 & 4 are  In Running and Show 1 has Ended)

01/01/2019 06:00:00          01/01/2019 06:59:59                      4 (Show 2, 3 & 4 are  In Running and Show 5 has Started)

01/01/2019 07:00:00          01/01/2019 07:59:59                      2 (Show 4 & 5 are In Running and Show 2 & 3 have Ended) 

01/01/2019 08:00:00          01/01/2019 08:59:59                      1 (Show 5 is In Running and Show 4 has Ended)

01/01/2019 09:00:00          01/01/2019 09:59:59                      1 (Show 5 is In Running)

01/01/2019 10:00:00          01/01/2019 10:59:59                      0 (No Shows are Running)

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. 

InRunning = COUNTROWS (

FILTER (

'Shows',

OR (

'Shows'[UTC_START_TIME]>= EARLIER ( [DateTime] )

&& 'Shows'[UTC_START_TIME] <= EARLIER ( [End Of Period Date/Time] ),

'Shows'[UTC_END_TIME] >= EARLIER ( [DateTime] )

&& 'Shows'[UTC_END_TIME] <= EARLIER ( 'DATE'[End Of Period Date/Time] )

)

)

)

I appreciate your help here

Thanks

Joe

[AlB · Accepted solution]

Hi @Anonymous 

Try this for a new calculated column in your DateTable:

InRunning =
COUNTROWS (
    FILTER (
        'Shows',
        'Shows'[UTC_END_TIME] > 'DATE'[DateTime]
            && 'Shows'[UTC_START_TIME] < 'DATE'[End Of Period Date/Time]
    )
)

[Anonymous]

@AlB 

Thank you.

It worked a dream.

All the best 

Joe