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

johnt75's avatar
johnt75
Super User
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