Median for Days between Status Dates in same table

[PhilC]

Hello,

I would like to calculate the Median # Days between Application Submitted (AS) Date to Success (S) Date from a table of Statuses.

Key Business Rules

• There may be multiple occurrences of a Status for an ID, use MIN date
• Exclude where there is no AS status
• Exclude where there is an AS status and no S status
• Exclude where the S date is less than the AS date

Desired answer is Median of 116.5, based on workings in Excel sheet attached.

Reading through the code below I have definitely confused myself while trying different things to get this to work.  Read at your peril.

Median =
VAR ApplicationDate =
    CALCULATE (
        MIN ( 'Status Dates'[STATUPDATE_FLD] ),
        'Status Dates'[STATUS_FLD] = "AS"
    )
VAR SuccessDate =
    CALCULATE (
        MIN ( 'Status Dates'[STATUPDATE_FLD] ),
        'Status Dates'[STATUS_FLD] = "S"
    )
VAR ExcludeBadDates =
    IF ( ApplicationDate > SuccessDate, 1, 0 )
VAR SuccessTable =
    CALCULATETABLE (
        'Status Dates',
        'Status Dates'[STATUPDATE_FLD] = ApplicationDate
            || 'Status Dates'[STATUPDATE_FLD] = SuccessDate
            && ExcludeBadDates = 0
    )
VAR AverageTimetoSuccess =
    MEDIANX (
        FILTER (
            VALUES ( 'Status Dates'[UID_FLD] ),
            CALCULATE ( COUNTROWS ( 'Status Dates' ), 'Status Dates'[STATUS_FLD] = "AS" ) > 0
                && CALCULATE ( COUNTROWS ( 'Status Dates' ), 'Status Dates'[STATUS_FLD] = "S" ) > 0
                && ExcludeBadDates = 0
        ),
        DATEDIFF ( ApplicationDate, SuccessDate, DAY )
    )
RETURN
    AverageTimetoSuccess

Data is below (cannot see how to attached PBIX and Excel file, will try to upload when at home)

STATUPDATE_FLD	STATUS_FLD	UID_FLD
1/08/1990	AS	8
22/11/1990	S	8
16/12/1994	S	13
22/11/1994	S	13
24/11/1994	CR	13
25/11/1994	CS	13
1/12/1994	CG	13
21/01/1994	DS	13
4/02/1994	DR	13
16/12/1994	IS	13
27/07/1993	AS	2
2/06/1994	S	15
19/12/1994	CR	15
2/06/1994	CS	15
30/07/1993	AS	11
6/09/1993	P	11
22/09/1993	AR	11
14/08/1995	EI	20
14/08/1995	ES	20
3/11/1995	CR	20
7/11/1995	CS	20
10/11/1995	CG	20
13/11/1995	IS	20
26/05/1993	AR	15
26/05/1993	AS	15
12/09/1995	SE	20
31/12/1995	CF	13
15/02/1994	AR	13
24/11/1993	S	2
2/10/1995	AR	20
31/12/1995	Z	15
28/02/1994	AS	13
8/12/1995	S	20
5/11/1993	S	11
2/10/1995	AS	20
31/12/1991	Z	8
11/09/2000	OW	20
30/09/2000	D	2
31/12/1997	Z	2
26/06/1998	C	13
18/05/2001	Z	13
24/07/2001	Z	20
24/03/2000	C	11
2/08/2000	FF	11
6/08/2001	Z	11
8/08/2001	FF	20
30/09/2000	D	8
31/12/2004	D	15
31/12/2004	D	2
24/07/2009	D	20
13/06/2016	AS	40
10/06/2016	S	40
15/06/2016	AS	50
20/06/2016	G	50

Would appreciate any guidance.

Cheers

Phil

[ImkeF · Accepted solution]

Hi @PhilC 

please check this code:

MyMedian =
VAR FilterRel =
    FILTER (
        'Status Date',
        OR ( 'Status Date'[STATUS_FLD] = "AS", 'Status Date'[STATUS_FLD] = "S" )
    )
VAR GroupOnUID =
    ADDCOLUMNS (
        SUMMARIZE ( FilterRel, 'Status Date'[UID_FLD] ),
        "Date", CALCULATE ( MIN ( 'Status Date'[STATUPDATE_FLD] ) ),
        "UID", 'Status Date'[UID_FLD],
        "S", CALCULATE (
            MIN ( 'Status Date'[STATUPDATE_FLD] ),
            'Status Date'[STATUS_FLD] = "S"
        ),
        "AS", CALCULATE (
            MIN ( 'Status Date'[STATUPDATE_FLD] ),
            'Status Date'[STATUS_FLD] = "AS"
        )
    )
VAR Selection =
    FILTER (
        ADDCOLUMNS ( GroupOnUID, "TimeRange", ( [S] - [AS] ) * 1 ),
        [TimeRange] >= 0
            && NOT ( ISBLANK ( [AS] ) )
    )
VAR Result =
    MEDIANX ( Selection, [TimeRange] )
RETURN
    Result

[PhilC]

Hi Imke, thanks so much for the quick response.  That code is producing the expected result with the test data, and also with the original dataset, awesome (especially as I did not even have time to post the file).

Now to try to understand the approach 🙂

Thanks again.

Cheers, Phil