Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

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 ...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

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

v-echaithra's avatar
v-echaithra
Community Support
1 year ago

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.