# Difference between two dates

• ### 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

### 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 Wednesday, June 3, 2015 1:20 PM
• Marked as answer by 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.

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

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 Wednesday, June 3, 2015 1:20 PM
• Marked as answer by Monday, June 8, 2015 11:37 AM
• Unmarked as answer by 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