Measure for day count between 2 dates

Hiii RichOB 

• Identify when a kennel is empty:

  • Find the latest Tenancy_End for a kennel.
  • Find the next Tenancy_Start for the same kennel.
  • Calculate the difference (days empty).

• Handle missing end dates:

  • If a kennel is still occupied (no Tenancy_End), ignore it for now.
    
    
    
    

    Days_Empty =
    VAR CurrentKennel = SELECTEDVALUE(Table2[KennelNumber])
    VAR TenancyEndDates =
    FILTER(
    Table2,
    Table2[KennelNumber] = CurrentKennel &&
    NOT(ISBLANK(Table2[Tenancy_End]))
    )

    VAR NextStartDates =
    FILTER(
    Table2,
    Table2[KennelNumber] = CurrentKennel &&
    NOT(ISBLANK(Table2[Tenancy_Start]))
    )

    VAR EmptyDays =
    SUMX(
    TenancyEndDates,
    VAR EndDate = Table2[Tenancy_End]
    VAR NextStartDate =
    MINX(
    FILTER(NextStartDates, Table2[Tenancy_Start] > EndDate),
    Table2[Tenancy_Start]
    )
    RETURN IF(NOT(ISBLANK(NextStartDate)), DATEDIFF(EndDate, NextStartDate, DAY), 0)
    )

    RETURN EmptyDays

Hi RichOB ,

To calculate the number of empty days per kennel, the DAX measure needs to identify gaps between when one dog leaves and the next one arrives. The measure first determines the Tenancy_End date of a dog in a specific kennel. Then, it looks for the earliest Tenancy_Start date of another dog that arrives after the Tenancy_End date in the same kennel. If a new dog starts immediately, the empty days would be zero; otherwise, the difference in days is calculated using DATEDIFF. If no new dog arrives, the measure returns blank.

Days_Empty_Measure = 
VAR CurrentKennel = SELECTEDVALUE(Table2[KennelNumber])
VAR CurrentEndDate = MAX(Table2[Tenancy_End])
VAR NextStartDate =
    CALCULATE(
        MIN(Table2[Tenancy_Start]),
        Table2[KennelNumber] = CurrentKennel,
        Table2[Tenancy_Start] > CurrentEndDate
    )
RETURN 
    IF(NOT(ISBLANK(NextStartDate)), DATEDIFF(CurrentEndDate, NextStartDate, DAY), BLANK())

For example, in Kennel DS_1, Max1 left on April 20, 2024, and Piper1 was the next to arrive on April 25, 2024, meaning the kennel was empty for four days. In Kennel DS_2, Bob1 left on September 1, 2024, and Tilly1 started on November 1, 2024, resulting in a 61-day gap. If a kennel remains unoccupied after the last recorded Tenancy_End date, the measure does not assign a value.

Best regards,

Hi lbendlin thanks for your help. I tried this but I get an error saying "The start date or end date in Calendar function can not be BLANK value. Is this because some of the end dates haven't happened yet so those cells are blank?

Hi RichOB,

Thanks for reaching out to the Microsoft fabric community forum.

Just following up to your previous conversation, I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

I would also take a moment to thank lbendlin, Khushidesai0109 and DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.