locked
Difference between two dates RRS feed

  • Question

  • Hi,

    Is it possible to get difference between two dates - first one and last one per every group in table (records are sorted by ID)?

    ID Group Date
    739 Group 1 2015-04-17 14:27
    740 Group 1 2015-04-19 06:51
    760 Group 1 2015-04-19 11:51
    762 Group 1 2015-04-21 09:30
    763 Group 1 2015-04-20 09:20
    783 Group 1 2015-06-02 16:31
    808 Group 1 2015-05-08 13:49
    1125 Group 1 2015-05-08 14:35
    1127 Group 2 2015-05-11 12:09
    1128 Group 2 2015-05-11 12:03
    1129 Group 2 2015-05-11 09:07
    1130 Group 2 2015-05-08 20:10
    1131 Group 2 2015-05-11 06:20
    1132 Group 2 2015-05-11 11:33
    1133 Group 2 2015-05-08 14:43
    1134 Group 2 2015-05-08 14:42
    1135 Group 2 2015-05-11 07:08
    1177 Group 2 2015-05-12 10:42
    1180 Group 2 2015-05-12 11:27

    I've stucked on this challenge...

    Regards,

    Marcin

    Tuesday, June 2, 2015 10:30 PM

Answers

All replies

  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    Difference in days, hh:mm between first/last Group member.
    http://www.mediafire.com/view/er89wuffjo7ghc7/06_02_15.xlsx

    • Proposed as answer by Michael Amadi Wednesday, June 3, 2015 1:20 PM
    • Marked as answer by spq Monday, June 8, 2015 11:37 AM
    Wednesday, June 3, 2015 4:23 AM
  • See

    http://www.powerpivot-info.com/post/143-q-how-can-i-calculate-difference-between-two-dates-in-dax-seconds-minutes-hours-days-and-months

    If dates are in order you can use LASTDATE and FIRSTDATE to get start and end dates within group.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, June 3, 2015 6:07 AM
  • Hi Marcin,

    According to your description, you need to calculate the difference between the max date and min date in a group, right?

    I have tested it on my local environment, we can calculate Max date and Min date in the group by using the DAX expression below.
    =CALCULATE(MAX(case0603[Date]),ALLEXCEPT(case0603,case0603[Group]))
    =CALCULATE(MIN(case0603[Date]),ALLEXCEPT(case0603,case0603[Group]))
    And then calculate the difference using the DAX expression below.
    =1.0*(case0603[MaxDateByGroup]-case0603[MinDateByGroup])

    Or you can use the expression below directly.
    =1.0*((CALCULATE(MAX(case0603[Date]),ALLEXCEPT(case0603,case0603[Group])))-(CALCULATE(MIN(case0603[Date]),ALLEXCEPT(case0603,case0603[Group]))))

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Wednesday, June 3, 2015 1:20 PM
    • Marked as answer by spq Monday, June 8, 2015 11:37 AM
    • Unmarked as answer by spq Monday, June 8, 2015 11:37 AM
    Wednesday, June 3, 2015 6:49 AM
  • It works - and it is so simple with filtering:)

    Thank you very much.

    Regards,

    Marcin,

    Monday, June 8, 2015 11:39 AM