End date count

[RichOB]

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 classified as a zero.

Property	Start_Date	End_Date	Tenant
1 Sacramento Dr	01/09/2023	01/09/2024	Dave
1 Sacramento Dr	10/09/2024	01/09/2025	Mark

As more than 1 day passed from Grant moving out and Helen moving in, this can be classified as a 1

Property	Start_Date	End_Date	Tenant
1 Sacramento Dr	01/09/2023	01/09/2024	Grant
1 Sacramento Dr	11/09/2024	01/09/2025	Helen

How can this be achieved, please?

Thanks

[danextian]

Hi @RichOB 

Try the following

Classification = 
VAR _property = 'Table'[Property]
VAR _tenant = 'Table'[Tenant]
VAR _currentStartDate = 'Table'[Start_Date]
VAR _tbl =
    FILTER (
        'Table',
        'Table'[Property] = _property
            && 'Table'[Tenant] <> _tenant
            && 'Table'[End_Date] < _currentStartDate
    )
VAR _prevEndDate =
    MAXX ( _tbl, [End_Date] )
VAR _daysLapsed =
    DATEDIFF ( _prevEndDate, 'Table'[Start_Date], DAY )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _prevEndDate ), BLANK (),
        IF ( _daysLapsed > 1, 1, 0 )
    )

Note: Your sample tables will both return 1 as there is a 9 days gap from Sep1 to Sep10 in the first table.

[RichOB]

Hi @danextian I think this has worked. There's one adjustment I need. Sometimes the new start date is before expiry date, like in this example:

Property	Start Date	Expiry Date	Classification
1 Sacramento Dr	13/10/2020	13/10/2021	1
1 Sacramento Dr	14/10/2021	14/10/2022	0
1 Sacramento Dr	24/10/2022	24/10/2023	1
1 Sacramento Dr	20/10/2023	20/10/2024	1
1 Sacramento Dr	25/10/2024	25/10/2025	1

The start date in October 2023 is 4 days before the expiry of the previous tenancy. In this circumstance, I would need it to show as a 0. How can that be done, please?

[danextian · Accepted solution]

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]

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]

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]

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,

[MasonMA]

Hi, 

In Dane's code, this can be done by adding a condition '_currentStartDate <= _prevEndDate, 0, ' after ISBLANK line. 

[johnt75]

I think you can use OFFSET to get the next row for the current property, e.g.

Left empty =
VAR CurrentEndDate = 'Table'[End_Date]
VAR NextStartDate =
    SELECTCOLUMNS (
        OFFSET (
            1,
            ALL ( 'Table'[Property], 'Table'[Start_Date] ),
            ORDERBY ( 'Table'[Start_Date], ASC ),
            PARTITIONBY ( 'Table'[Property] )
        ),
        'Table'[Start_Date]
    )
VAR Result =
    IF (
        (
            ISBLANK ( NextStartDate )
                || DATEDIFF ( CurrentEndDate, NextStartDate, DAY ) > 1
        )
            && CurrentEndDate < TODAY (),
        1,
        0
    )
RETURN
    Result