Identify Earliest Records

[Saes]

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!

[johnt75]

You could add a calculated column like

Letting number =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[Property ID] ),
    'Table'[Tenancy Start Date],
    ASC
)

[Saes · Accepted solution]

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

        )