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.   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.

 

 

 

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.

5 Replies

  • 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