Forum Discussion

Saes's avatar
Saes
Helper I
3 years ago
Solved

Identify 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 P...
Need Help
  • Saes's avatar
    Saes
    3 years ago

    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
            )
johnt75's avatar
johnt75
Super User
3 years ago

You could add a calculated column like

Letting number =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[Property ID] ),
    'Table'[Tenancy Start Date],
    ASC
)
  • Saes's avatar
    Saes
    Helper I
    3 years ago

    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
            )