Forum Discussion
End date day count
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!
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 ResultRichOB 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)
)
5 Replies
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 ResultRichOB 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)
)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 EHi 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. 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.
