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.

     

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

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Order Number calculated table = 
VAR _maxrowcount =
    MAXX (
        ADDCOLUMNS (
            VALUES ( Sales[Customer] ),
            "@rowcount",
                COUNTROWS (
                    WINDOW (
                        1,
                        ABS,
                        -1,
                        ABS,
                        Sales,
                        ORDERBY ( Sales[Index], ASC ),
                        ,
                        PARTITIONBY ( Sales[Customer] ),
                        MATCHBY ( Sales[Customer], Sales[Index] )
                    )
                )
        ),
        [@rowcount]
    )
RETURN
    SELECTCOLUMNS( GENERATESERIES ( 1, _maxrowcount, 1 ), "Order Number", [Value] )

     

     

    order date tracking: = 
VAR _t =
    GENERATE (
        VALUES ( Sales[Customer] ),
        VALUES ( 'Order Number calculated table'[Order Number] )
    )
VAR _result =
    ADDCOLUMNS (
        _t,
        "@orderdate",
            MAXX (
                INDEX (
                    'Order Number calculated table'[Order Number],
                    Sales,
                    ORDERBY ( Sales[Order Date], ASC, Sales[Index], ASC ),
                    ,
                    ,
                    MATCHBY ( Sales[Index] )
                ),
                Sales[Order Date]
            )
    )
RETURN
    MAXX ( _result, [@orderdate] )

     

    diff vs previous measure: = 
VAR _currentorderdate = [order date tracking:]
VAR _previousorderdate =
    CALCULATE (
        [order date tracking:],
        OFFSET (
            -1,
            ALL ( 'Order Number calculated table'[Order Number] ),
            ORDERBY ( 'Order Number calculated table'[Order Number], ASC )
        )
    )
VAR _condition =
    INT ( _currentorderdate <> BLANK () && _previousorderdate <> BLANK () )
RETURN
    DIVIDE ( INT ( _currentorderdate - _previousorderdate ), _condition )

     

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

      Jihwan_Kim   I think you've solved it.  Gimme a moment to try to adapt your dax to my actual data.

    • 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.