Forum Discussion

ivan_abboud's avatar
ivan_abboud
Frequent Visitor
2 years ago
Solved

Dynamic RANKX Based on Date Slicer

Hello Community

 

I have an order history table that shows the order per customer and the corresponding date of order

I want to create a rank for each customer order to know which was the first order of that customer, second, etc..

I've managed to do that using the following Dax formula

RankPerCustomer =
RANKX(
    FILTER(
        ALL('receipts'),
        'receipts'[customer_id] = EARLIER('receipts'[customer_id])
    ),
    'receipts'[date_created_local],
    ,
    ASC,
    Dense
)
and it's working fine as shown in the following
 

 

The problem is that I have a date slicer filter and I want these ranks to be recalculated based on the filtered data

whenever I'm trying to change the date range, the ranks will never change, and it will be calculated based on the whole data, I want to recalculate the rank based on the specified date range

for example in the following to show 1 and 2 for "ivan" as per the specified date this was his first and second order

 

I tried multiple solutions but nothing seemed to work
I appreciate your help guys,

Thanks in advance

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi ivan_abboud 
    Please refer to the linked pbix and recreate a logic , for your data.

    Note, that you have to use the "Dim date" table.

     

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

6 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi ivan_abboud 
    Please refer to the linked pbix and recreate a logic , for your data.

    Note, that you have to use the "Dim date" table.

     

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

    • ivan_abboud's avatar
      ivan_abboud
      Frequent Visitor
      2 years ago

      Hi Ritaf1983 
      Thanks for the solution, but, actually this is not what I'm looking for

       

      I need to rank orders based on their date, per customer
      so for each customer, I want to see his first purchase, second, and so on... and I managed to do this using a calculated column and dax as I explained previously. 
      My problem now is that I want to get this data filtered by date and recalculate the ranks based on that

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        If you still need help, then share the download link of the PBI file.