Providing the Relet date for a tenancy.

09 Sunday Nov 2014

Posted by in Uncategorized

≈ Leave a comment

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.