Increments of 1 for consecutive records and process to repeat after breaks

[Mann]

Hi Guys,

I am trying to implement this logic but couldn't make it so far. This is as follows:

I have a table with Individual IDs and Date. I am trying to calculate a column which should show increments of 1 for consecutive records per Individual ID and break if concequtive records for a particular Individual ID is not present. It should repeat the above process if again consecutive records are present.

Following is the example. Column is blue is expected one:

For e.g: For Individual ID: 111, for consecutive records from 8/8/2018 to 8/11/2018 increments of 1 is there starting from 0. For 8/13/2018 it breaks as this is not consecutive to 8/11/2018 so values is 0. It again starts from 8/21/2018 to 8/24/2018.

Please advice!

[v-cherch-msft · Accepted solution]

Hi @Mann 

You may add an index column first.Then create below columns to it.

Column =
VAR pre_dates =
    CALCULATE (
        MAX ( Table1[Dates] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Individual ID] ),
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
        )
    )
RETURN
    IF ( Table1[Dates] = pre_dates + 1, 0, 1 )

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

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

Regards,

[Mann]

Hi,

This works as expected. It was great help! I just need to subtract RANK by 1 as I want order to start from 0.

I have one more question: Can this be done using measures? (Seems like that I want to achieve at last)

I know EARLIER() doesn't work in measures as like calculated columns, but can this be done dynamically using measures?

For e.g. If some filters are selected then on the returned result this final column should comes as per same logic?

Thanks