Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
L0gan
New Member

Help to calculate days at a particular status

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!

 

Data Sample.png

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@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]
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

3 REPLIES 3
L0gan
New Member

Thank you so much, worked perfectly!

Anonymous
Not applicable

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
Super User
Super User

@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]
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.