Count Specific Time Slots within Time range

[Gsar]

Hey Everybody.

I am new and have never posted in the forum before, but I have been using it a lot to have some of my questions answered.

I have tried to add a sample file for my problem but it seems that I am not able to do it.

I have this data set that contains  a a Time Start and a Time End.

I want to be able to count the specific half hour time slots that are contained in those time ranges.

Table Transactions

Date	random variable	Time Start	Time End	Week	id
25-06-2020	c1	08:30	10:00	1	1
26-06-2020	c1	08:00	17:00	1	2
27-06-2020	c2	09:00	12:00	2	3
28-06-2020	c2	09:15	12:30	2	4
29-06-2020	c1	10:00	11:30	1	5
30-06-2020	c3	13:15	16:40	3	6
01-07-2020	c3	12:00	17:00	4	7
02-07-2020	c4	10:40	13:35	4	8

 Table 2: Time slots

Time Start	Time End
08:00	08:30
08:30	09:00
09:00	09:30
09:30	10:00
10:00	10:30
10:30	11:00
11:00	11:30
11:30	12:00
12:00	12:30
12:30	13:00
13:00	13:30
13:30	14:00
14:00	14:30
14:30	15:00
15:00	15:30
15:30	16:00
16:00	16:30
16:30	17:00

For example, how many 8-8:30 time slots do I have in my data.

What I have done so far and works is cross join the the tables and filter like below.

Extend = filter(CROSSJOIN('time slots','Transactions'),(Time slots[Time Start] <= 'Transactions'[Time End] && Time slots[Time End] >= 'Transactions'[Time Start]))

This allows me to count the time slots but the new extende table is getting too big.

I have also tried to count the rows directly in the Time slots table but then I cannot use the calculation for anything else.

What I need is a measure that will help me count the rows for the specific time slots. 

I want to create a bar chart where the breakdown will be by Time slots.

Bascially, I will neeed this measure to countrows the same way that it would do as if I had the cross joined table.

[VahidDM · Accepted solution]

Hi @Gsar 

Try this measure:

Count Time Slot = 
VAR _A =
    ADDCOLUMNS(
        'Time slots',
        "C",
            COUNTROWS(
                FILTER(
                    'Table Transactions',
                    'Time slots'[Time Start]  < 'Table Transactions'[Time END]
                       && 'Time slots'[Time End] > 'Table Transactions'[Time Start]
                )
            )
    )
RETURN
    SUMX( _A, [C] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Gsar]

I worked on top of your solution and got the expected result. 

I changed the measure to the following

Measure = 
VAR _start =
    MAX ( 'Time slots'[Time Start] )
VAR _end =
    MAX ( 'Time slots'[Time End] )
VAR _count =
    COUNTROWS (
        FILTER (
            Transactions,
             _start< Transactions[Time END]
                && _end > Transactions[Time Start]
        )
    )
RETURN
    _count

 Now I am getting the following results which looks correct?

Time Start	Time End	Measure
08:00:00	08:30:00	1
08:30:00	09:00:00	2
09:00:00	09:30:00	4
09:30:00	10:00:00	4
10:00:00	10:30:00	4
10:30:00	11:00:00	5
11:00:00	11:30:00	5
11:30:00	12:00:00	4
12:00:00	12:30:00	4
12:30:00	13:00:00	3
13:00:00	13:30:00	4
13:30:00	14:00:00	4
14:00:00	14:30:00	3
14:30:00	15:00:00	3
15:00:00	15:30:00	3
15:30:00	16:00:00	3
16:00:00	16:30:00	3
16:30:00	17:00:00	3
total		3

My issue now is in the total.

The total should be 62 but I am getting 3. I should be able to use this measure to count the rows if I drop it to the weeks or the random variable.

For example:

c1	15
c2	20
c3	21
c4	7
total	62

The above is just an example but I hope that you understand the meaning.

Please let me know whether I should open another thread for this aditional question. 

I would be happy to accept your answer as correct the way it is since you helped immensely to build the one I was looking for. 

[smpa01]

@Gsar  can you please try this measure, pbix is attached

Measure = 
VAR _start =
    MAX ( 'Time slots'[Time Start] )
VAR _end =
    MAX ( 'Time slots'[Time End] )
VAR _count =
    COUNTROWS (
        FILTER (
            Transactions,
            Transactions[Time Start] >= _start
                && _end <= Transactions[Time END]
        )
    )
RETURN
    _count

[Gsar]

Hey smpa01. 

I am afraid this counting is not what I am looking for. 

If you look at id = 2 then the time range is from 8-17, which means that there is only one time slot from 8-8:30.

However, no other id has a starting time from 8 which means that the result for the time slot from 8-8:30 should be 1.

As another example, lets take the time slot from 8:30-9.

In the transaction data, the time slot of 8:30-9 is included on in the id 1,2 so the expected result should be 2.

Please let me know if it makes sense.

Best 🙂