Conditionally Counting Rows and Assigning Number to Row

[Anonymous]

Hi all,

I have a list of payment attempts, and I'd like to add in the attempt number for each row. A "string" of attempts would be where the date is equal to or is the next day. When you get to a day where this is not the case, the counter needs to "reset". This would be for each ID. So there should be a reset per ID as well as the break in the date string.

For example, for the data below I would like the final data to look something like this, starting only with the first two columns:

Initially I created a "unique ID" by combining the month number and the ID, and grouped by this ID to get the count, but I'd like to be able to assign the attempt number to each date and this also didn't work for attempt strings that went over two months.

Just can't figure it out, any help would be welcome.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may add a index column first. Then you may get the rank with below dax.

Column =
IF (
    Table1[DateCreated]
        = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 )
        || Table1[DateCreated]
            = LOOKUPVALUE ( Table1[DateCreated], Table1[Index], Table1[Index] - 1 )
                + 1,
    0,
    1
)

Column 2 =
SUMX (
    FILTER ( Table1, Table1[Index] <= EARLIER ( Table1[Index] ) ),
    Table1[Column]
)

Rank =
RANKX (
    FILTER ( Table1, Table1[Column 2] = EARLIER ( Table1[Column 2] ) ),
    Table1[Index],
    ,
    ASC
)

Regards,

Cherie