Forum Discussion
DATEDIFF with EARLIER Function
I am trying to calculate the datediff between transactions. I have a table with both a DeviceID and TransDate column. There are lots of transactions which take place randomly across the different devices (DeviceID). I would like to calculate the number of days between "TransDate" per DeviceID. I've tried using a combination of DATEDIFF and EARLIER functions, but I am unable to get it to work.
Ultimately, can I use a filer of somes ort to lookup the previous row where the DeviceID matches the current row and then run DATEDIFF against the same column?
Hey,
i guess you can use this DAX statement to create a Calculated Column
DateDiff Previous TransactiondDate = DATEDIFF( CALCULATE( MAX(Table1[TransactionDate]) ,FILTER( ALL('Table1') ,'Table1'[DeviceID] = EARLIER(Table1[DeviceID]) && 'Table1'[TransactionDate] < EARLIER(Table1[TransactionDate]) ) ) ,'Table1'[TransactionDate] ,DAY )If you use Power BI or an Excel version that supports variables you can also this variant, there is no performance difference in both variants, but personally I prefer to use variables because I thing variables make more complex statements more readable. And often there is also a performance improvement because variables are able to cash the result of an expression, and hence will be evaluated just once, but this of course depends from the given situation.
variables DateDiff Previous TransactiondDate = var currentDeviceID = 'Table1'[DeviceID] var currentTransactionDate = 'Table1'[TransactionDate] return DATEDIFF( CALCULATE( MAX(Table1[TransactionDate]) ,FILTER( ALL('Table1') ,'Table1'[DeviceID] = currentDeviceID && 'Table1'[TransactionDate] < currentTransactionDate ) ) ,'Table1'[TransactionDate] ,DAY )Hopefully this is what you are looking for.
Regards,
Tom
2 Replies
Hey,
i guess you can use this DAX statement to create a Calculated Column
DateDiff Previous TransactiondDate = DATEDIFF( CALCULATE( MAX(Table1[TransactionDate]) ,FILTER( ALL('Table1') ,'Table1'[DeviceID] = EARLIER(Table1[DeviceID]) && 'Table1'[TransactionDate] < EARLIER(Table1[TransactionDate]) ) ) ,'Table1'[TransactionDate] ,DAY )If you use Power BI or an Excel version that supports variables you can also this variant, there is no performance difference in both variants, but personally I prefer to use variables because I thing variables make more complex statements more readable. And often there is also a performance improvement because variables are able to cash the result of an expression, and hence will be evaluated just once, but this of course depends from the given situation.
variables DateDiff Previous TransactiondDate = var currentDeviceID = 'Table1'[DeviceID] var currentTransactionDate = 'Table1'[TransactionDate] return DATEDIFF( CALCULATE( MAX(Table1[TransactionDate]) ,FILTER( ALL('Table1') ,'Table1'[DeviceID] = currentDeviceID && 'Table1'[TransactionDate] < currentTransactionDate ) ) ,'Table1'[TransactionDate] ,DAY )Hopefully this is what you are looking for.
Regards,
Tom