Forum Discussion

Martin_Bruwer's avatar
Martin_Bruwer
Frequent Visitor
8 years ago
Solved

Calculate difference between rows

Hello All,   I'm trying to calculate the number of days between two appointments where the first appointment could be one of two types. in the below table it would be the days between the earlier o...
Need Help
Tutorial Requests
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Martin_Bruwer

     

    Measure =
VAR EarlierAppointment =
    CALCULATE (
        MIN ( TableName[Appintment Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Patient ID] ),
            TableName[Appointment Type] = "Type 1"
                || TableName[Appointment Type] = "Type 2"
        )
    )
VAR Type3Date =
    CALCULATE (
        FIRSTNONBLANK ( TableName[Appintment Date], 1 ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Patient ID] ),
            TableName[Appointment Type] = "Type 3"
        )
    )
VAR Start_date =
    MIN ( EarlierAppointment, Type3Date )
VAR End_date =
    MAX ( EarlierAppointment, Type3Date )
RETURN
    DATEDIFF ( Start_date, End_date, DAY )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Depends on if you need the values as a column, or if a measure would suffice (measures are preferred from a performance standpoint).

 

Try this measure:

[Measure] =
VAR CurrentPatient =
    VALUES ( TableName[Patient ID] )
VAR Appointment3Date =
    CALCULATE (
        MIN ( TableName[Appointment Date] ),
        TableName[Appointment Type] = "Type 3",
        TableName[Patient ID] IN CurrentPatient
    )
VAR EarliestAppointmentDate =
    CALCULATE (
        MIN ( TableName[Appointment Date] ),
        TableName[Appointment Type] IN { "Type 1", "Type 2" },
        TableName[Patient ID] IN CurrentPatient
    )
RETURN
    Appointment3Date - EarliestAppointmentDate

Variables make it easy to call out what each part of the calculation is doing.

  • Martin_Bruwer's avatar
    Martin_Bruwer
    Frequent Visitor
    8 years ago

    Thank you Zubair_Muhammad and Anonymous

     

    Chris,

     

    I used your measure but made a slight change to the return,

     

    Changed your to

     

      DATEDIFF(EarliestAppointmentDate,AppointmentDate,day)

     

    Giving me days as opposed to a date formate.

     

    It appears to be working, time to do testing.

     

    Thank you so much.

    • Martin_Bruwer's avatar
      Martin_Bruwer
      Frequent Visitor
      8 years ago

      Anonymous

       

      Hi Again,

       

      Immidiately ran into two issues.

       

      1. Because of the nature of the business it is possible to have a patient who has appointment type 3 prior to appointment types 1 or 2. Using Datediff I get the error that the start date cannot be greater than the end date.  I could not use datediff but I need to have a result in days rather than a date.
      2. The result is also needed to be and Median and Mean over many hundreds of patients, with the measure I'm not sure where to put the function in.

       

      Thank you again.

       

      Martin

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Martin_Bruwer

         

        Measure =
VAR EarlierAppointment =
    CALCULATE (
        MIN ( TableName[Appintment Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Patient ID] ),
            TableName[Appointment Type] = "Type 1"
                || TableName[Appointment Type] = "Type 2"
        )
    )
VAR Type3Date =
    CALCULATE (
        FIRSTNONBLANK ( TableName[Appintment Date], 1 ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Patient ID] ),
            TableName[Appointment Type] = "Type 3"
        )
    )
VAR Start_date =
    MIN ( EarlierAppointment, Type3Date )
VAR End_date =
    MAX ( EarlierAppointment, Type3Date )
RETURN
    DATEDIFF ( Start_date, End_date, DAY )