Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

End date count

Hi, using the examples below, I need a count of 1 when a property is not moved into the day after someone has previously left.   As Mark moved in the day after Dave moved out, this can be classifie...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Try this:

    Classification = 
VAR _property = 'Table'[Property] -- Current property
VAR _tenant = 'Table'[Tenant] -- Current tenant
VAR _currentStartDate = 'Table'[Start_Date] -- Current start date
VAR _tbl =
    FILTER('Table', 'Table'[Property] = _property && 'Table'[Tenant] <> _tenant) -- Other rows for same property, different tenant
VAR _startDateBeforeCurrent =
    MAXX(FILTER(_tbl, 'Table'[Start_Date] < _currentStartDate), [Start_Date]) -- Latest start date before current
VAR _prevEndDate01 =
    MAXX(FILTER(_tbl, 'Table'[End_Date] < _currentStartDate), [End_Date]) -- Latest end date before current start
VAR _prevEndDate02 =
    MAXX(FILTER(_tbl, 'Table'[Start_Date] = _startDateBeforeCurrent), [End_Date]) -- End date of entry with the previous start date
VAR _finalEndDate =
    IF(_currentStartDate <= _prevEndDate02, _prevEndDate02, _prevEndDate01) -- Pick most relevant previous end date
VAR _daysLapsed =
    DATEDIFF(_finalEndDate, 'Table'[Start_Date], DAY) -- Days between final end and current start
VAR _Result =
    SWITCH(
        TRUE(),
        ISBLANK(_finalEndDate), BLANK(),
        IF(_daysLapsed > 1, 1, 0)
    ) -- 1 if gap > 1 day, else 0

RETURN
    _Result

     

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

Hi RichOB 

Just checking in to see if the issue you raised regarding the tenant move-in/move-out gap calculation has been resolved. danextian MasonMA   response that addressed your scenario where a 1 is counted only if more than a day has passed between a tenant moving out and the next moving in.

 

Could you please confirm if that solution worked for you? If not, feel free to share any additional details or edge cases you're dealing with, and we’ll be happy to assist further.

 

Looking forward to your confirmation!

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

Hi RichOB 

Just following up on your query regarding identifying gaps between tenants based on move-in and move-out dates.

As danextian has already shared a response addressing your example where a count of 1 is returned only if more than one day has passed between tenants (like in the Grant → Helen case), and 0 when they move in the next day (Dave → Mark).

 

Were you able to test the solution in your dataset? If you're still facing issues or need help adapting it further, feel free to share your progress I’m happy to assist!

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    1 year ago

    Hi RichOB 

    Just a quick reminder on your thread regarding the tenant transition logic for the property at 1 Sacramento Dr.

    You were looking to count cases where a property is not reoccupied the day after someone moves out, and classify it as “1” if there’s a gap, and “0” if the new tenant moves in the next day.

    To recap your examples:

    • Dave ➝ Mark (moved in the next day) = 0

    • Grant ➝ Helen (moved in after a gap) = 1

    Both danextian MasonMA  have already provided answers on how to implement this logic, including DAX examples and comparison techniques based on Start_Date and End_Date.

    Please review their suggestions and let us know if any clarification is still needed.

     

    If we don’t hear back , we’ll go ahead and close the thread in line with the community guidelines.

    Thanks for your time and cooperation!

    Best regards,