Forum Discussion
Anonymous
8 years agoNot applicable
Rolling DateDiff to compare dates within a Table
Hi, I need to calculate date diff within a table to the prior row, Here's a sample dataset: CustomerID;OrderID;OrderDate;DaysBetweenOrders 123;12;'2009-04-01';0 123;12;'2009-04-23';22 123...
- 8 years ago
this code below almost works - the issue is it gives 3 for both rows dated 2009-06-04
in order to solve this you would need to either
1) add row id as a column and use that as criteria instead of date
2) have time of the order as well as the date, so you could specify that orders did not happen in the same momentColumn = VAR CurrentCustomerID = 'Table'[CustomerID] VAR CurrentOrderID = 'Table'[OrderID] VAR CurrentDate = 'Table'[OrderDate] VAR PreviousDate = CALCULATE ( LASTDATE ( 'Table'[OrderDate] ), FILTER ( ALL ( 'Table' ), 'Table'[CustomerID] = CurrentCustomerID && 'Table'[OrderID] = CurrentOrderID && 'Table'[OrderDate] < CurrentDate ) ) RETURN IF ( PreviousDate = 0, 0, CurrentDate - PreviousDate )
Stachu
Community Champion
8 years agothis code below almost works - the issue is it gives 3 for both rows dated 2009-06-04
in order to solve this you would need to either
1) add row id as a column and use that as criteria instead of date
2) have time of the order as well as the date, so you could specify that orders did not happen in the same moment
Column =
VAR CurrentCustomerID = 'Table'[CustomerID]
VAR CurrentOrderID = 'Table'[OrderID]
VAR CurrentDate = 'Table'[OrderDate]
VAR PreviousDate =
CALCULATE (
LASTDATE ( 'Table'[OrderDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[CustomerID] = CurrentCustomerID
&& 'Table'[OrderID] = CurrentOrderID
&& 'Table'[OrderDate] < CurrentDate
)
)
RETURN
IF ( PreviousDate = 0, 0, CurrentDate - PreviousDate )