Forum Discussion

JGroothedde's avatar
JGroothedde
Icon for Resolver II rankResolver II
1 year ago
Solved

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

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 sta...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
JGroothedde
Icon for Resolver II rankResolver II
1 year ago

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
            )
        )
    )
)