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 )
Anonymous
Hi Again,
Immidiately ran into two issues.
- 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.
- 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
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 )Thank you very much, I believe I have a solution now. Trial and error.