cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mann Helper V
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:

 

Output.PNG

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-cherch-msft
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)

1.png

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.

View solution in original post

2 REPLIES 2
Microsoft v-cherch-msft
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)

1.png

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.

View solution in original post

Mann Helper V
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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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