Generate consecutive calculated column from scratch

[Syndicate_Admin]

Good morning community.

Today I ask for help with a calculated column for a table, where I have a customer (ID_CLIENTE) that can be repeated several times and different sales dates (FECHA_VENTA), I need to generate a consecutive (CONSECUTIVE in red) starting from scratch (0) for each row where the customer repeats (ID_CLIENTE) and in ascending order of sales date (FECHA_VENTA), attached table example result:

ID_CLIENTE	FECHA_VENTA	CONSECUTIVE
1	01/02/2021	1
1	22/03/2021	2
1	01/01/2021	0
2	03/06/2021	0
2	24/06/2021	1
2	27/06/2021	2
3	03/04/2021	1
3	02/02/2021	0
3	05/04/2021	2
3	06/04/2021	3

Thank you very much in advance for your support.

WEC

[parry2k]

@Syndicate_Admin add new measure using following:

Rank1 = 
RANKX ( 
    FILTER( 
        ALLSELECTED ( 'Rank' ), 
        'Rank'[ID_CLIENTE] = MAX ( 'Rank'[ID_CLIENTE] ) 
    ), 
    CALCULATE ( 
        MIN ( 'Rank'[FECHA_VENTA] ) 
    ), , 
    ASC 
)  - 1 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Syndicate_Admin]

Thank you very much for your prompt response!

Could you help me but as a calculated column? Thank you very much in advance.

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

Try this code to build a calculated column. You can use Earilier function to catch current row.

CONSECUTIVE = 
RANKX(FILTER('Table','Table'[ID_CLIENTE] = EARLIER('Table'[ID_CLIENTE])),'Table'[FECHA_VENTA],,ASC,Dense)-1

Result is as below.

Best Regards,
Rico Zhou

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

[Syndicate_Admin]

Thank you very much for example! It helped me to solve this challenge... greetings from Guatemala. 🤓