Find count of rows between start-end date in same table

[JGroothedde]

Hi there,

I'm having a brainfart for something that i initially thought would be very easy. I hope someone can help me with this.

I have a single table like so:

ID person	datetime start	datetime planned end	status
1	01-10-24 09:00	01-10-24 14:00	completed
2	08-11-24 13:00	08-11-24 23:00	completed
3	10-10-24 09:00	10-10-24 17:00	cancelled
1	02-10-24 10:00	02-10-24 14:00	completed
3	10-10-24 10:30	10-10-24 14:00	completed

What i'm trying to do is find the count of rows for each person and row that either began or ended in the timeframe between the start and end datetime. For example, what i'm looking for is this:

ID person	datetime start	datetime planned end	status	# between start-end
1	01-10-24 09:00	01-10-24 14:00	completed	0
2	08-11-24 13:00	08-11-24 23:00	completed	0
3	10-10-24 09:00	10-10-24 17:00	cancelled	1
1	02-10-24 10:00	02-10-24 14:00	completed	0
3	10-10-24 10:30	10-10-24 14:00	completed	0

How do i go about this? Many thanks in advance!

[v-denglli-msft · Accepted solution]

Hi @JGroothedde ,

Please refers to the following steps.

1. Create two dimension tables for the /datetime start/ field and the /datetime planned end/ field

DimStartime = VALUES('Table'[datetime start])

DimEndtime = VALUES('Table'[datetime planned end])

2. Creates a measure that calculates the count of rows.

Test = 
VAR _result = 
IF(SELECTEDVALUE('DimEndtime'[datetime planned end])>SELECTEDVALUE('DimStartime'[datetime start]),
CALCULATE(
     COUNTROWS('Table'),
     KEEPFILTERS('Table'[datetime planned end]<=SELECTEDVALUE('DimEndtime'[datetime planned end])&&'Table'[datetime start]>=SELECTEDVALUE(DimStartime[datetime start]))
     ,'Table'[status]="Completed"
     )
    )
    RETURN
IF(_result<>BLANK(),1,0)

3. Create slicers using fields from both dimension tables.
The result is shown below and hopefully meets your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JGroothedde]

Thank you @v-denglli-msft i have no clue why i didn't think of creating dimtables in the first place. Lifesaver, cheers!

[JGroothedde]

I'm trying something like this now. It works, but is extremely slow. Anyone have any tips to speed this up? 🙂

Test = 

VAR _date = FORMAT(SELECTEDVALUE(Orders[Datetime start]),"dd-MM-yyyy")
VAR _starttime = FORMAT(SELECTEDVALUE(Orders[Datetime start]),"HH:mm")
VAR _endtime = FORMAT(SELECTEDVALUE(Orders[Datetime planned end]),"HH:mm")
VAR _idperson = SELECTEDVALUE(Orders[ID person])
VAR _idorder = SELECTEDVALUE(Orders[ID Order])

RETURN

CALCULATE(
    [Count of orders],
    ,FILTER(
        ALL(Orders),
        Orders[ID Person] = _idperson &&
        Orders[ID Order] <> _idorder &&
        Orders[Date planned] = _date &&
        Orders[Status] = "Completed" &&
        OR(
            AND(
             FORMAT(Orders[Datetime start],"HH:mm") >= _starttime
            ,FORMAT(Orders[Datetime start],"HH:mm") <= _endtime
            )
            ,
            AND(
             FORMAT(Orders[Datetime planned end],"HH:mm") >= _starttime
            ,FORMAT(Orders[Datetime planned end],"HH:mm") <= _endtime
            )
        )
    )
)