Forum Discussion

etane's avatar
etane
Icon for Helper V rankHelper V
2 years ago
Solved

Request DAX For Ranked Order Date Tracking for Different Orders on Same Date

Hello.   I am trying to track order velocity of how fast a customer gets to order number 2, 3, 4 and so on.  So, I need to calculate differences between two order dates.    However, with the curr...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    thank you for your message, and pleaes check the below pictures and the attached pbix file.

     

etane's avatar
etane
Icon for Helper V rankHelper V
2 years ago

Jihwan_Kim 

I've successfully adapted your DAX to my actual data and it works!.  However, there's certain aspect of the original request that isn't fulfilled.  More precisely, I needed a single DAX that calculates:

1) the number of days it takes a customer to reorder (which is what I currently have)
2) takes into account of multiple orders on the same day (which is why I needed help)

3) is it possible to generate a DAX that captures only the second order date for all cutomers?  ie:
2nd Order Date = calculate(min(order date tracking), filter( order number = 2))
This is based on the order date tracking table you provided:

 



I am hoping to get this single Order Date DAX so I can calculate the difference between various Orders such as between Orders 1 and 2 or between Orders 1 and 3 and it shows both the days by single customer and average by all customers such as below:  

 

 

I apologize since I wasn't clear about this ask in the beginning but would you be able to accomodate this ask?

 

Thanks.

 




 

 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

thank you for your message, and pleaes check the below pictures and the attached pbix file.

 

  • etane's avatar
    etane
    Icon for Helper V rankHelper V
    2 years ago

    Jihwan_Kim 

     

    Hello.

     

    I was able to adapt your DAX to my actual model.  And, it accurately depicts dates by Order #.  However, I can't write a measure that shows the average days it takes from one order to the other.  

    Below, the datediff works in calculating days it takes to make next order:

    However, it doesn't work when I apply an iterative function to it to try to get the average days:

    Could you please tell me what I might be missing in the "Days to 2" DAX above?  

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      2 years ago

      Hi,
      I am not sure what visualization is used in you image.

       

      expected result measure: =
AVERAGEX (
    VALUES ( Sales[Customer] ),
    DATEDIFF ( [Order 1 date], [Order 2 Date], DAY )
)

       

       

      • etane's avatar
        etane
        Icon for Helper V rankHelper V
        2 years ago

        Thank you!  Exactly what I am looking for: