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

[etane]

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!

[v-heq-msft]

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]

@v-heq-msft 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? 

[etane]

@Jihwan_Kim 

Hello.  

I've added index to the PQ editor and am able to create a Rank based on Order Date and Index.  

However, I am not sure how to capture (1st, 2nd) order dates while taking Customer, Order Date and Rank into consideration, .  Could you please review the sample file?

https://www.dropbox.com/scl/fi/w5v9qxy7si8ri50uax1sf/Test-Rank.pbix?rlkey=xnkmo391z8w2lpdpenpe1v068&...

[Jihwan_Kim]

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]

@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 · Accepted solution]

Hi,

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

[etane]

@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]

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]

Thank you!  Exactly what I am looking for:

[etane]

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

[Jihwan_Kim]

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.