Forum Discussion

Gsar's avatar
Gsar
Frequent Visitor
4 years ago
Solved

Count Specific Time Slots within Time range

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 i...
  • VahidDM's avatar
    VahidDM
    4 years ago

    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's avatar
Gsar
Frequent Visitor
4 years ago

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 StartTime EndMeasure
08:00:0008:30:001
08:30:0009:00:002
09:00:0009:30:004
09:30:0010:00:004
10:00:0010:30:004
10:30:0011:00:005
11:00:0011:30:005
11:30:0012:00:004
12:00:0012:30:004
12:30:0013:00:003
13:00:0013:30:004
13:30:0014:00:004
14:00:0014:30:003
14:30:0015:00:003
15:00:0015:30:003
15:30:0016:00:003
16:00:0016:30:003
16:30:0017:00:003
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:

c115
c220
c321
c47
total62

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.