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 ...
  • johnt75's avatar
    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
    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)
    )