Forum Discussion

Jayadev's avatar
Jayadev
Helper I
8 years ago
Solved

Calculating difference between dates comparing previous row

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

    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's avatar
    Jayadev
    8 years ago

    Hi,

     

    Super solution, thanks a lot,  it works perfect. 

     

    Warmest regards Jay, Once again Thanks.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
Jayadev
Helper I
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      Jayadev
      Helper I
      8 years ago

      Hi,

       

      Super solution, thanks a lot,  it works perfect. 

       

      Warmest regards Jay, Once again Thanks.