Forum Discussion
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...
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 )
Try this calculated column
Column =
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"
)
)
RETURN
DATEDIFF ( EarlierAppointment, Type3Date, DAY )