Index / Rank

[Mateusz00]

Hello, I have three columns (ID, Time and Status). I would like to have some calculated column which shows me for each ID index / rank until the status is "Closed" (based on time column).

ID                Time                 Status           Index

1                 01/01/2023       Other            1

1                 02/01/2023       Closed          1

1                 03/01/2023       Other            2

1                 04/01/2023       Other            2

1                 05/01/2023       Other            2

1                 06/01/2023       Closed          2
1                 07/01/2023       Other            3

2                 06/01/2023       Other            1
2                 07/01/2023       Closed          1

2                 08/01/2023       Other            2

2                 09/01/2023       Other            2

2               10/01/2023       Closed            2

[Anonymous]

Hi, @Mateusz00 

Based on the information you have provided, Here are my answers to your questions.

1. Create a table of your presentation.

2. Create a calculated column, enter the appropriate DAX code and filter out the Index with Status of Closed.

Column =

    VAR currentID = 'Table'[ID]

    VAR currentStatus = 'Table'[Status]

RETURN

IF(currentStatus = "Closed",

    RANKX(

        FILTER('Table', 'Table'[ID] = currentID && 'Table'[Time] <= EARLIER('Table'[Time]) && 'Table'[Status] = "Closed"),

        'Table'[Time],

        ,

        ASC,

        DENSE

    ),

    BLANK()

)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mateusz00]

Hello, thank you for your response but how can we complete each rows? (in this formula we have blanks and I must have data exactly like in my Index column from the sample).

[Dangar332 · Accepted solution]

hi, @Mateusz00 

try below code 

RANKX(
    FILTER(
        'Table','Table'[id]=EARLIER('Table'[id]) &&
        'Table'[time]<=EARLIER('Table'[time])&&
        'Table'[status]="closed"
    ),
    'Table'[time],,ASC,Dense
)

[Mateusz00]

Topic got to the spam folder earlier. Can somebody helps me?