Forum Discussion
Saes
Helper I
Helper IIdentify Earliest Records
Hi all,
I've got a set of tenancy lettings data and I need to categorise a lettings order for each property. For example, the data I have looks like this:
| Tenancy ID | Tenancy Start Date | Property ID | Address |
| TEN001 | 01/04/2005 | PROP001 | 1 PowerBI Street |
| TEN002 | 21/06/2007 | PROP001 | 1 PowerBI Street |
| TEN003 | 21/10/2007 | PROP001 | 1 PowerBI Street |
| TEN004 | 04/08/2017 | PROP002 | 2 Computer Close |
| TEN005 | 23/06/2021 | PROP002 | 2 Computer Close |
| TEN006 | 25/10/2021 | PROP002 | 2 Computer Close |
And I need to add a calculated column that shows this:
| Tenancy ID | Tenancy Start Date | Property ID | Address | Tenancy Letting ID |
| TEN001 | 01/04/2005 | PROP001 | 1 PowerBI Street | First Let |
| TEN002 | 21/06/2007 | PROP001 | 1 PowerBI Street | Second Let |
| TEN003 | 21/10/2007 | PROP001 | 1 PowerBI Street | Third Let |
| TEN004 | 04/08/2017 | PROP002 | 2 Computer Close | First Let |
| TEN005 | 23/06/2021 | PROP002 | 2 Computer Close | Second Let |
| TEN006 | 25/10/2021 | PROP002 | 2 Computer Close | Third Let |
I've tried several combinations of DAX, but I've not been successful. Can anyone help?
Thanks!
Thank you - I had to make a tweak, but it worked. The final formula I've used is here:
Tenancy Letting Order =RANKX (FILTER (ALL ( 'Lettings Data'), [Property ID] = EARLIER ( 'Lettings Data'[Property ID] ) ),'Lettings Data'[Tenancy ID],,ASC,DENSE)
2 Replies
johnt75Super User
You could add a calculated column like
Letting number = RANKX ( ALLEXCEPT ( 'Table', 'Table'[Property ID] ), 'Table'[Tenancy Start Date], ASC )
SaesThank you - I had to make a tweak, but it worked. The final formula I've used is here:
Tenancy Letting Order =RANKX (FILTER (ALL ( 'Lettings Data'), [Property ID] = EARLIER ( 'Lettings Data'[Property ID] ) ),'Lettings Data'[Tenancy ID],,ASC,DENSE)