Forum Discussion
End date count
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
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.
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?
Hi,
In Dane's code, this can be done by adding a condition '_currentStartDate <= _prevEndDate, 0, ' after ISBLANK line.
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 _ResultHi 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!
