Forum Discussion

PhilC's avatar
PhilC
Icon for Resolver I rankResolver I
6 years ago
Solved

Median for Days between Status Dates in same table

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...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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

     

     

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    PhilC
    Icon for Resolver I rankResolver I
    6 years ago

    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