I wanted to get some information on a importing process where i load different files into a database. I don’t have access to the database i only get CSV export files from the admins.
I have two date fields where i want to see the difference between them in seconds (should only take seconds). In SQL Server i would have used DateDiff function and be done with it. No such function is available in DAX so we have to be creative ourselves. I loaded the CSV into PowerPivot and decided to add new measurs in PowerPivot window.
The first solution i came up with was to try and subtract one date from the other, this seemed to work. So now i wanted to get only the seconds of this subtraction:
=SECOND(<datefield1> – <datefield2>)
This was the result i hoped for.
But being a developer I wasn’t satisfied with this result, when the process would run for more than 60 seconds the minute would be ignored, so you could make it into:
=SECOND(<datefield1> – <datefield2>) + (MINUTE(<datecolum1> – <datecolum2>)*60)
But again what happens when the process runs more than 60 minutes.. after a great discussion on twitter with Vidas on how to solve this problem, he came up with (thanks to Marius Dumitru (MSFT))
86400. * ( [DateField1] – [DateField2] )
First number because: (24*60*60 = 86400 seconds per day)
This is a pretty rock solid formula that would give you the difference in seconds between two dates regardless the interval.
One conclusion Vidas and I got to was when a serious challenge arises PowerPivot is not really suited for a fast solution. You would be better off fixing it in your source or be very creative 🙂 like the solution above. Maybe the default Excel user is better suitable for these challenge because of their experience with Excel.Or maybe PowerPivot needs a different mindset than the defensive programming we developers are used to do, trying to be prepared for all the possible exceptions, and rely on assumptions (like the time difference is always seconds) and be done with it.
Ofcourse in the future we will have new functions, this will make our work easier.