Forum Discussion
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...
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
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