Forum Discussion
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 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
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
8 Replies
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 ResultHi 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.
