Calculating difference between dates comparing previous row

[Jayadev]

Hi

I have a table containing patients admission and discharge details. The objective is to find out patients re-admitted after discharge.  If re-admitted in less than 5 days it should be alerted.   I have calculated in excel manually, how to write DAX to achieve this result ?  Just a comparison with previous row will not help,  It should check the same paitent re-admission. In below example there is 1 patient re-admitted after 1 day.

Please help to find out a solution.

The sample data prepared in excel is given in the attached image.

[Zubair_Muhammad · Accepted solution]

@Jayadev

Re Admission =
VAR PreviousDischargeDate =
    CALCULATE (
        MAX ( TableName[DISCHARGEDATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[PatientID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousDischargeDate ) ),
        IF (
            PreviousDischargeDate < TableName[AdmitDate],
            DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY )
        )
    )

Try this revised formula

[Zubair_Muhammad]

Hi @Jayadev

Try this solution

First add an Index Column for each PATIENTID. This will help in further calculations

Index =
RANKX (
    FILTER ( TableName, [PatientID] = EARLIER ( [PatientID] ) ),
    [AdmitDate],
    ,
    ASC,
    DENSE
)
Code beutified with Dax Formatter by SQLBI

Now you can get the Re-admission calculated column using this formula

Re Admission =
VAR PreviousDischargeDate =
    CALCULATE (
        MAX ( TableName[DISCHARGEDATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[PatientID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousDischargeDate ) ),
        DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY )
    )

[Jayadev]

Thanks a lot for your valuable inputs.  I am getting the following error.  Please help.

In DATEDIFF function, the start date cannot be greater than the end date

Regards,

Jay

[Zubair_Muhammad · Accepted solution]

@Jayadev

Re Admission =
VAR PreviousDischargeDate =
    CALCULATE (
        MAX ( TableName[DISCHARGEDATE] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[PatientID] ),
            TableName[Index]
                = EARLIER ( TableName[Index] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousDischargeDate ) ),
        IF (
            PreviousDischargeDate < TableName[AdmitDate],
            DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY )
        )
    )

Try this revised formula

[Jayadev · Accepted solution]

Hi,

Super solution, thanks a lot,  it works perfect. 

Warmest regards Jay, Once again Thanks.

[Zubair_Muhammad]

@Jayadev