Forum Discussion
Time between dates for each unique identifier - several date variables
Hi
I’m new to Power BI and having some troubles coming up with a way to calculate the average number of days passed between two dates with the data below, for a case management DB. The difference between the dates needs to be calculated in two different ways, considering closed cases only:
- difference between the “initial_contact_date” for cases that were opened and closed on the same day (“Survivor_closed_case”=”yes”), which means the case only has one submission/row. In this case the difference will be zero as it is the same day. See for example "editedorg1-30".
- difference between the “initial_contact_date” in the first submission and the “Follow_up_date” of the last submission/row, for cases when the latter was closed (“Survivor_closed_case”=”yes”)
Description of data:
- One row = one case submitted
- Each row has a unique identifier (“Unique_ID_merged”)
- Each case (identified by “Unique_ID_merged”) may have one or multiple rows_
- Each case will have the first submission (“New_or_registered”=“new_case”). If the case is closed, it will take the value “yes” to the “Survivor_closed_case”). This type of submission will have a date as value for “initial_contact_date”). The date of case closure will be the same as “initial_contact_date”. See "org2-30" in the data
- Each case may have follow-up submissions (“New_or_registered”= “tracking_existing case”) and, if the case is closed, it will take the value “yes” to the “Survivor_closed_case”). For these submissions, we only have a date value as the “Follow_up_date”, and we can only know the “initial_contact_date” from the first submission, linking them through the “Unique_ID_merged”. See "org1-16" in the data.
This will help us know how long, on average, it takes to close one case of violence once the survivor has decided to close it. Thanks 😍 After this, I will try to calculate the average number of days a case has been open, for cases that have not been closed yet (see "org3-10" in the data)
| Unique_ID_merged | New_or_registered | Initial_contact_date | Follow_up_date | Survivor_closed_case |
| org3-10 | new_case | 20/08/2024 | null | no |
| org3-10 | tracking_existing_case | null | 21/08/2024 | no |
| org1-16 | new_case | 27/08/2024 | null | no |
| org1-16 | tracking_existing_case | null | 28/08/2024 | no |
| org1-16 | tracking_existing_case | null | 30/08/2024 | yes |
| org2-30 | new_case | 06/09/2024 | null | yes |
AvgDaysToClose=
Averagex(
Values( datum[Unique_ID_merged] ),
Var initialContact =
Calculate( min( datum[initial_contact_date ))
Var closedInitialContact =
Calculate(
min( datum[inital_contact_date),
Datum[Survivor_closed_case] = "yes"
)
Var lastFollowup =
Calculate(
Max( datum[follow_up_date),
Datum[Survivor_closed_case] = "yes"
)
Return
Datediff(initialContact, coalesce (closedInitialContact, lastFollowup ))
)
Hi, many thanks! Just a couple of tweaks, in particular, the third argument for DATEDIFF. Here is what worked!
Avgdaysclosure =AVERAGEX(VALUES('DB'[Unique_ID_merged]),VAR initialContact =CALCULATE(MIN('DB'[initial_contact_date]))VAR closedInitialContact =CALCULATE(MIN('DB'[initial_contact_date]),'DB'[Survivor_closed_case] = "yes")VAR closedLastFollowup =CALCULATE(MAX('DB'[follow_up_date]),'DB'[Survivor_closed_case] = "yes")RETURNDATEDIFF(initialContact, COALESCE(closedInitialContact, closedLastFollowup),DAY))
3 Replies
- Deku
Super User
AvgDaysToClose=
Averagex(
Values( datum[Unique_ID_merged] ),
Var initialContact =
Calculate( min( datum[initial_contact_date ))
Var closedInitialContact =
Calculate(
min( datum[inital_contact_date),
Datum[Survivor_closed_case] = "yes"
)
Var lastFollowup =
Calculate(
Max( datum[follow_up_date),
Datum[Survivor_closed_case] = "yes"
)
Return
Datediff(initialContact, coalesce (closedInitialContact, lastFollowup ))
)
Hi, many thanks! Just a couple of tweaks, in particular, the third argument for DATEDIFF. Here is what worked!
Avgdaysclosure =AVERAGEX(VALUES('DB'[Unique_ID_merged]),VAR initialContact =CALCULATE(MIN('DB'[initial_contact_date]))VAR closedInitialContact =CALCULATE(MIN('DB'[initial_contact_date]),'DB'[Survivor_closed_case] = "yes")VAR closedLastFollowup =CALCULATE(MAX('DB'[follow_up_date]),'DB'[Survivor_closed_case] = "yes")RETURNDATEDIFF(initialContact, COALESCE(closedInitialContact, closedLastFollowup),DAY))Thanks so much for your help. I wanted to do a similar thing for the cases that remained open so both 'DB'[initial_contact_date]), 'DB'[Survivor_closed_case] = "no" AND 'DB'[follow_up_date]), 'DB'[Survivor_closed_case] = "no"), using TODAY().
Does the below make sense? Many thanks again!
Avgdaysopen = AVERAGEX( FILTER( 'DB', 'DB'[Survivor_closed_case] = "no" ), VAR OpenInitialContact = CALCULATE( MIN('DB'[initial_contact_date]) ) RETURN DATEDIFF(OpenInitialContact, TODAY(), DAY) )