Create a column to rank the dates of the period

[jobf]

Please see the table below:

Field	Period	Date
P1	2324	10/01/2024
P1	2324	10/01/2024
P1	2324	11/02/2024
P1	2425	02/08/2025
P1	2425	01/15/2025
P2	2324	05/05/2024
P2	2324	04/01/2024
P2	2425	01/01/2025
P2	2425	02/10/2025
P2	2425	02/10/2025
P2	2425	01/25/2025

This table shows the dates that a certain product was applied to a certain field. I need a column that shows the application number that occurred on that date of the especific period. It should look like this:

Field	Period	Date	Application
P1	2324	10/01/2024	1
P1	2324	10/01/2024	1
P1	2324	11/02/2024	2
P1	2425	02/08/2025	2
P1	2425	01/15/2025	1
P2	2324	05/05/2024	2
P2	2324	04/01/2024	1
P2	2425	01/01/2025	1
P2	2425	02/10/2025	3
P2	2425	02/10/2025	3
P2	2425	01/25/2025	2

If two lines have the three identical pieces of information, the application number must be the same.

[Bibiano_Geraldo]

Hi @jobf ,

To achieve your goal, please, create a new calculated column by this DAX:

Application = 
RANKX(
    FILTER(
        'Table',
        'Table'[Field] = EARLIER('Table'[Field]) &&
        'Table'[Period] = EARLIER('Table'[Period])
    ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

This will return this result:

[jobf]

The problem is that sometimes there will be more than one line with the same field, period and date. I needed the code to then put the same application number in both equal lines.

[Bibiano_Geraldo]

Hi, can you provide a sample data including that scenario and the updated desired result?

[Bibiano_Geraldo]

Hi @jobf ,

But you can try to rank only the unique dates within each Field and Period.:

Application = 
VAR UniqueRank = 
    RANKX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE('Table', 'Table'[Field], 'Table'[Period], 'Table'[Date]), 
                "Rank", 'Table'[Date]
            ),
            'Table'[Field] = EARLIER('Table'[Field]) &&
            'Table'[Period] = EARLIER('Table'[Period])
        ),
        'Table'[Date],
        ,
        ASC,
        DENSE
    )

RETURN UniqueRank

The output will look like this:

[jobf]

I updated the statement and tables.

[Bibiano_Geraldo · Accepted solution]

Hi @jobf ,

Use this DAX:

Application = 
VAR UniqueRank = 
    RANKX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE('Table', 'Table'[Field], 'Table'[Period], 'Table'[Date]), 
                "Rank", 'Table'[Date]
            ),
            'Table'[Field] = EARLIER('Table'[Field]) &&
            'Table'[Period] = EARLIER('Table'[Period])
        ),
        'Table'[Date],
        ,
        ASC,
        DENSE
    )

RETURN UniqueRank

The output will look like this:

[wini_R]

Hey @jobf,
Please check if this thread helps to solve your issue:
https://community.fabric.microsoft.com/t5/Desktop/TOP-N-by-few-columns/m-p/4406944#M1372085 

[wini_R]

Just an alternative solution, if anyone was interested, for adding calculated column using RANK function: