none
Measure to calculate the difference in time as integer in PowerPivot

    Question

  • Hello,

    I've read and figured out how to calculate the the difference between dates (in seconds) as a calculated column, but I get an error when I try to create it as a measure. 

    As posted on (http://www.powerpivot-info.com/post/143-q-how-can-i-calculate-difference-between-two-dates-in-dax-seconds-minutes-hours-days-and-months) I'm using =Round(24*60*60*(date2-date1),1) and it works pefect as a calculuated column. What I really want to do is create a measure doing the same thing, but I get an error, probably because I'm not creating an aggregate. 

    I'm working on creating formulas to measure response times for our fire department. The overall goal is to create a dashboard in PowerPivot, but I'm starting out small and will build as I learn. 

    I should add an image of the  data for a complete picture. 

    

    Thank you for any help.

    Brent


    • Edited by bvanscoy678 Saturday, July 26, 2014 4:17 PM
    Saturday, July 26, 2014 2:47 PM

Answers

  • Hello,

    Apparatus will be a large table (my standards - up to a million rows if I do multiple years). I was able to correctly use your measure in my dataset!  I adjusted and used MINX because I'm really looking for the minimum time it takes a unit to arrive on location. I can take the minimum time and compare to the goal of 200 seconds to return a True or False for the incident. The most accurate answer is I'm not entire certain how the dashboard will look yet, but creating the initial measures to look at an incident, calculate the response time and then returning the True or False will be my first goal. Below is an example of the measure in action. The 2014- 42XXXXXXX is the incident unique Key and below that list the units on the call. I would gladly upload a copy of  the book, but I don't see an attachment for file and I'm not certain using drop box is allowed on the site. I'll need to look at the rules closer. 

    Thank you! Brent

    • Marked as answer by bvanscoy678 Saturday, July 26, 2014 10:06 PM
    Saturday, July 26, 2014 9:21 PM

All replies

  • The best measure will really depend on your ultimate goal and the layout of the final pivot table.

    The first thing you can try is this:

    Difference:=
    SUMX(
      Apparatus,
      Round(24*60*60*(date2-date1),1)
    )

    This should work but it might not be the fastest if Apparatus is an extremely large table.

    If you provide an example or what your final pivot for reporting this will look like then a more optimized version can most likely be created.

    Saturday, July 26, 2014 8:46 PM
    Answerer
  • Hello,

    Apparatus will be a large table (my standards - up to a million rows if I do multiple years). I was able to correctly use your measure in my dataset!  I adjusted and used MINX because I'm really looking for the minimum time it takes a unit to arrive on location. I can take the minimum time and compare to the goal of 200 seconds to return a True or False for the incident. The most accurate answer is I'm not entire certain how the dashboard will look yet, but creating the initial measures to look at an incident, calculate the response time and then returning the True or False will be my first goal. Below is an example of the measure in action. The 2014- 42XXXXXXX is the incident unique Key and below that list the units on the call. I would gladly upload a copy of  the book, but I don't see an attachment for file and I'm not certain using drop box is allowed on the site. I'll need to look at the rules closer. 

    Thank you! Brent

    • Marked as answer by bvanscoy678 Saturday, July 26, 2014 10:06 PM
    Saturday, July 26, 2014 9:21 PM