dynamic median filter

Hi SammyNed ,

I create a table as you mentioned.

Then I create two calculated columns.

TimeToNewState = DATEDIFF([Time moved to New State], TODAY(), DAY)

TimeToContactedState = DATEDIFF([Time moved to New State], [Time moved to Contacted State], DAY)

Next I think you can create two measures and a calculated column.

MedianTimeToContacted = MEDIANX(
    FILTER(
        'Table',
        NOT(ISBLANK([TimeToContactedState]))
    ),
    [TimeToContactedState]
)

IsOutsideMedian = IF(
    AND(
        [Current State] = "New",
        [TimeToNewState] > [MedianTimeToContacted]
    ),
    1,
    0
)

LeadsOutsideMedian = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        [IsOutsideMedian] = 1
    )
)

Best Regards

Yilong Zhou

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