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 currect DAX I am using, I am not accurately counting multiple orders that occur on the same date.  Basically, I am only counting the first order and omitting the rest for orders that occur on the same date.

 

Unfornuately, our order date doesn't have time stored or that could be used as tie breaker.

 

So, below is a simplified model.  I'd like to be able to take Product and Status in consideration in the DAX too:

 

Please assist.  Thanks!




11 Replies

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

    Hi,

    In my opinion, one of ways to add tie-breaker column is, adding index column in Power Query editor. I think this helps to differenciate the same data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi etane ,
    Thanks to Jihwan_Kim for the solution. Here is my solution
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Copy the following code into the advanced editor of Power Query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUQooyk8pTS5RcASyXVJzMstSi1JTgGxDfUN9IwMjY6VYHaJ1GJGow4hkO4zIsMMYiw4jvP4wIVmHOc11AMPKlC5azEjWYmQA1RILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Table" = _t, Product = _t, Status = _t, #"Order Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Table", type text}, {"Product", type text}, {"Status", type text}, {"Order Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sales Table", "Customer"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Customer"}, {{"Count", each _, type table [Customer=nullable text, Product=nullable text, Status=nullable text, Order Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product", "Status", "Order Date", "index"}, {"Custom.Product", "Custom.Status", "Custom.Order Date", "Custom.index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count", "Custom.Product"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.Order Date", type date}})
in
    #"Changed Type1"

    Create a measure

    Measure = 
CALCULATE(
    DATEDIFF(MIN('Sales Table'[Custom.Order Date]),MAX('Sales Table'[Custom.Order Date]),DAY),
    ALLEXCEPT(
        'Sales Table',
        'Sales Table'[Custom.index]
    )
)

    Final output

    Best regards,

    Albert He

     

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

     

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

      Anonymous Thank you for your assistance.  I have a couple of questions.

       

      1) I've been trying to find a rank formula that resets to 1 by customer and you've seen to done so in your base table.  How did you do that?

      2) I am trying to capture first, second ... orders perhaps by rank after it resets by customer.  Then, calculate the days difference between each order.  I think you're trying to do that but I am not sure what your "measure" results actually reflect? 

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