Forum Discussion
Helper VRequest DAX For Ranked Order Date Tracking for Different Orders on Same Date
Hi,
thank you for your message, and pleaes check the below pictures and the attached pbix file.
Helper VJihwan_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?
Super UserHi,
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 think you've solved it. Gimme a moment to try to adapt your dax to my actual data.
- 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
Hi,
thank you for your message, and pleaes check the below pictures and the attached pbix file.
- etane
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?