Tags
Providing the Relet date for a tenancy.
A number of properties are rented. The property system provides the start and end date for each tenancy together with the property reference. The objective is to provide the date tenancy is relet in an additional column.
I have previously solved this by importing a duplicate table, creating a rank on property lets on both tables and matching rows.
Tenancy | Property Reference | Tenancy start Date | Tenancy End date |
1000 | 100 | 01/02/2013 | 01/03/2013 |
1001 | 100 | 15/04/2013 | 07/06/2013 |
1002 | 101 | 01/04/2013 | 27/08/2013 |
1003 | 100 | 15/07/2013 | |
1004 | 102 | 01/05/2013 | 01/07/2014 |
1005 | 101 | 25/09/2013 | |
1006 | 102 | 01/09/2013 |
Each property is relet on the tenancy start date as shown below.
Tenancy | Property Reference | Tenancy start Date | Tenancy End date | Relet Date |
1000 | 100 | 01/02/2013 | 01/03/2013 | 15/04/2013 |
1001 | 100 | 15/04/2013 | 07/06/2013 | 15/07/2013 |
1002 | 101 | 01/04/2013 | 27/08/2013 | 25/09/2013 |
1003 | 100 | 15/07/2013 | ||
1004 | 102 | 01/05/2013 | 01/07/2014 | 01/09/2013 |
1005 | 101 | 25/09/2013 | ||
1006 | 102 | 01/09/2013 |
The formula is
CALCULATE(
MIN( Tenancy[Tenancy start Date] ),
FILTER( Tenancy, Tenancy[Property Reference] = EARLIER( Tenancy[Property Reference] ) ),
FILTER( Tenancy, Tenancy[Tenancy start Date] > EARLIER( Tenancy[Tenancy start Date] ) )
)
The 2 filters provide a table for the MIN function on which to operate.
The 2 filters that select start dates for a property.
FILTER( Tenancy, Tenancy[Property Reference] = EARLIER( Tenancy[Property Reference] ) ),
This selects all of the rows where the property reference is the same as the current row.
Tenancy | Property Reference | Tenancy start Date | Tenancy End date |
1000 | 100 | 01/02/2013 | 01/03/2013 |
1001 | 100 | 15/04/2013 | 07/06/2013 |
1003 | 100 | 15/07/2013 |
FILTER( Tenancy, Tenancy[Tenancy start Date] > EARLIER( Tenancy[Tenancy start Date] ) )
This uses the table from the previous filter
Tenancy | Property Reference | Tenancy start Date | Tenancy End date |
1000 | 100 | 01/02/2013 | 01/03/2013 |
For this row select all rows where the tenancy start date is greater than the current row, which leaves the following table.
Tenancy | Property Reference | Tenancy start Date | Tenancy End date |
1001 | 100 | 15/04/2013 | 07/06/2013 |
1003 | 100 | 15/07/2013 |
Now the function that operates on the table finds the minimum value for the start dat
MIN( Tenancy[Tenancy start Date] ) provides the minimum start date from the table, i.e. the next tenancy start date
Note on the filters
An alternative is
FILTER( Tenancy,
Tenancy[Property Reference] = EARLIER( Tenancy[Property Reference] )
&& Tenancy[Tenancy start Date] > EARLIER( Tenancy[Tenancy start Date] )
)
The 2 separate filters are preferred as this provides a significantly faster response. This may be extremely slow for x rows whilst providing a reasonable response with 2 filters.