cancel
Showing results for
Did you mean:
Helper V

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

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

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

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

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

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

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors