End date day count

[RichOB]

Hi, I am looking for two things with the end dates:

1 - A day count between the End Date of one dog leaving a kennel, and the Start Date of the next dog entering that same kennel.

2 - If no dog has re-entered, I need the day count from the End Date to Today.

Location	Dog	Kennel	Start Date	End Date	Day Count
Manchester	Max	1	24/02/2024	04/03/2024	0
Manchester	Milo	1	04/03/2024	25/07/2024	233
Newcastle	Bob	2	12/07/2024	22/08/2024	4
Newcastle	Lola	2	25/08/2024	10/09/2024	186

What measure would get the Day Count column like the one, please?
Thanks!

[johnt75 · Accepted solution]

You could create a calculated column like

Day count =
VAR EndDate = 'Table'[End Date]
VAR NextStartDate =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Kennel] ),
        'Table'[Start Date] >= EndDate
    )
VAR Result =
    DATEDIFF ( EndDate, COALESCE ( NextStartDate, TODAY () ), DAY )
RETURN
    Result

[v-echaithra]

Hi @RichOB ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra.

[v-echaithra]

Hi @RichOB ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra.

[v-echaithra]

Thanks @bhanu_gautam , @johnt75  for Addressing the issue.

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithra E

[bhanu_gautam · Accepted solution]

@RichOB Create a calculated column to find the next start date for each kennel:

Next Start Date =
VAR CurrentKennel = 'Table'[Kennel]
VAR CurrentEndDate = 'Table'[End Date]
RETURN
CALCULATE(
MIN('Table'[Start Date]),
FILTER(
'Table',
'Table'[Kennel] = CurrentKennel &&
'Table'[Start Date] > CurrentEndDate
)
)

Create a calculated column to calculate the day count:

DAX
Day Count =
VAR NextStart = 'Table'[Next Start Date]
VAR CurrentEnd = 'Table'[End Date]
RETURN
IF(
ISBLANK(NextStart),
DATEDIFF(CurrentEnd, TODAY(), DAY),
DATEDIFF(CurrentEnd, NextStart, DAY)
)

[johnt75 · Accepted solution]

You could create a calculated column like

Day count =
VAR EndDate = 'Table'[End Date]
VAR NextStartDate =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Kennel] ),
        'Table'[Start Date] >= EndDate
    )
VAR Result =
    DATEDIFF ( EndDate, COALESCE ( NextStartDate, TODAY () ), DAY )
RETURN
    Result