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/

     

smpa01's avatar
smpa01
Community Champion
4 years ago

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

    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 🙂