Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Generate consecutive calculated column from scratch

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_CLIENTEFECHA_VENTACONSECUTIVE
101/02/20211
122/03/20212
101/01/20210
203/06/20210
224/06/20211
227/06/20212
303/04/20211
302/02/20210
305/04/20212
306/04/20213

Thank you very much in advance for your support.

WEC

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Thank you very much for your prompt response!

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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