Help to calculate days at a particular status

[L0gan]

Hi Dax Geniuses!

I am working on a PBI report for an animal shelter and I need to be able to calculate the number of days that animals are at particular statuses.  We have a Status Type which is either "In Care" which they are set as from when their record is created and "Outgoing" which signals the date they leave our care.  The "In Care" Status then has a variety of sub-statuses which shows the animal's status while they are with us (for example "Awaiting Vet Exam", "Under Vet Care" or "In Foster" etc.  I need to be able to calculate how many days each animal is at each status, so that I can then calculate things like the average number of days animals were "Available for Adoption" or "In Foster" during any given month/year.  I have my Fact Table below and the "Status Date2' Column has a one to many relationship with a Calendar Table, which is used to filter the results by month, year, etc. in the visuals/slicers.

As you will see from the example below, the animal's status can change frequently and the animal may leave our care and come back into our care later on.  I have been trying to solve this for weeks now, I would love to hear any ideas you have to achieve this!

Thanks!

[L0gan]

Thank you so much, worked perfectly!

[Anonymous]

Hi @L0gan,

Thank you for reaching out in Microsoft Community Forum.

Thank you  @bhanu_gautam  for the helpful responses.

As suggested by bhanu_gautham,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[bhanu_gautam · Accepted solution]

@L0gan 

First, create a calculated column to find the next status date for each animal. This will help in calculating the duration for each status.

NextStatusDate =
VAR CurrentAnimalID = 'FactTable'[AnimalID]
VAR CurrentStatusDate = 'FactTable'[Status Date2]
RETURN
CALCULATE(
MIN('FactTable'[Status Date2]),
FILTER(
'FactTable',
'FactTable'[AnimalID] = CurrentAnimalID &&
'FactTable'[Status Date2] > CurrentStatusDate
)
)

Next, create another calculated column to calculate the duration each animal spends in each status.

StatusDuration =
DATEDIFF(
'FactTable'[Status Date2],
COALESCE('FactTable'[NextStatusDate], TODAY()),
DAY
)

Now, you can create measures to calculate the average number of days for each status.

AverageDaysInStatus =
AVERAGEX(
'FactTable',
'FactTable'[StatusDuration]
)