Forum Discussion
Calculating time between despatching two orders
Anonymous
As mentioned earlier, there are duplicate dispatch dates in your data. To address this, sorting by order number is essential. If you sort by order number after the delivery date, you should obtain the desired result. If using the order number is not preferable, kindly specify your preferred sorting criteria for the dates.
It seems you're anticipating the previous row to display as it appears on the screen, but it's crucial to note that DAX requires proper sorting.
Anonymous
For DAX to find out the previous line, it has to appply sorting, since you have duplicate Despatch dates within each user, you need to include an additional column to have a unique record set, I added the order number in to this logic and you get the expected result. Add this as a column:
Previous =
VAR __User = Table9[Username ]
VAR __T = SUMMARIZE( ALLSELECTED( Table9 ) , Table9[Username ] , Table9[Despatch date] , Table9[Sales Order Number] )
RETURN
MAXX(
OFFSET( -1 ,
__T ,
ORDERBY(Table9[Username ],ASC,Table9[Despatch date],ASC,Table9[Sales Order Number],ASC ) ,
PARTITIONBY( Table9[Username ] ) ) , Table9[Despatch date]
)
Thank you for your answer!
I used the formula, and I am getting this, which still seems not correct, some values are okay, some are not. You can see the formula, is there something I did wrong?
Anonymous
As mentioned earlier, there are duplicate dispatch dates in your data. To address this, sorting by order number is essential. If you sort by order number after the delivery date, you should obtain the desired result. If using the order number is not preferable, kindly specify your preferred sorting criteria for the dates.
It seems you're anticipating the previous row to display as it appears on the screen, but it's crucial to note that DAX requires proper sorting.