Forum Discussion
etane
Helper V
2 years agoRequest 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...
- 2 years ago
Hi,
thank you for your message, and pleaes check the below pictures and the attached pbix file.
Jihwan_Kim
Super User
2 years agoHi,
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
Helper V
2 years agoJihwan_Kim I think you've solved it. Gimme a moment to try to adapt your dax to my actual data.