# DAX - Count time between two columns - with latest date

### Question

• Hello!

I am trying to calculate the time between two columns, where the second column's date (in the calculation) should be the latest...or most recent.  Moreover, over I want to do this for each of my unique ID values (ID, below).

Here is my table ('Time Study')...and what the Total Time calculated column should look like:

 ID Open Date Close Date Total Time (calculated column) John 7/10/2013 7/12/2013 4 days John 7/10/2013 7/14/2013 4 days Peter 7/10/2013 7/11/2013 5 days Peter 7/10/2013 7/13/2013 5 days Peter 7/10/2013 7/15/2013 5 days Matt 7/10/2013 7/13/2013 6 days Matt 7/10/2013 7/16/2013 6 days

Question:

How do I count time between the Open Date and the latest Close Date?

(I bolded the latest Close Date for each unique ID for you to see.)

• Edited by Sunday, November 24, 2013 3:48 PM
Sunday, November 24, 2013 3:48 PM

This should do it ;-)

```=INT(
CALCULATE(
LASTDATE(TimeStudy[Close Date]),
ALLEXCEPT('TimeStudy', TimeStudy[ID])
)
-
CALCULATE(
FIRSTDATE(TimeStudy[Open Date]),
ALLEXCEPT('TimeStudy', TimeStudy[ID])
)
)```

Notice how similar the newly added CALCULATE formula is; it behaves similarly to the LASTDATE version but this time it is looking for the first date in the [Open Date] column instead of the [Closed Date].

Wednesday, December 04, 2013 5:37 PM

### All replies

• Undergrads, did you have any success with this?

Thanks!

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Friday, November 29, 2013 11:22 PM

I'm not sure whether you have already solved this problem but below is a possible solution.

Note: I am in the UK so the date format I am using is dd/mm/yyyy.

I have taken your example data and created a Power Pivot table called 'TimeStudy'. For the calculated column called [Total Time (Days)], I have used the following DAX formula:

```=
INT(
CALCULATE(
LASTDATE(TimeStudy[Close Date]),
ALLEXCEPT('TimeStudy', TimeStudy[ID])
)
- TimeStudy[Open Date]
)```

The above formula clears all filters from the current row context except for the [ID] column and then returns the last date within this altered context, before calculating the difference between this and the current row's [Open Date] value.

For example, in the case of John, clearing all filters except the [ID] will return the dates 12/7/2013 and 14/7/2013. Out of these two dates, the last date would be 14/7/2013. We are then calculating the difference between this date and the value in the [Open Date] column for the current row which, in this example, would be 10/7/2013. The INT function ensures that we get the difference as a number i.e. the number of days.

Hope this helps.

Michael

Sunday, December 01, 2013 10:34 PM
• Michael (or anyone else),

One further question...

How would I add one additional criteria of selecting just the FIRSTDATE of 'Open Date' column?  Meaning, I want to calculate the time between FIRSTDATE of 'Open Date' column and LASTDATE of 'Close Date' for each unique ID.

This is what it should look like:

 ID Open Date Close Date Total Time (calculated column) John 7/10/2013 7/12/2013 5 days John 7/9/2013 7/14/2013 5 days Peter 7/8/2013 7/11/2013 7 days Peter 7/10/2013 7/13/2013 7 days Peter 7/10/2013 7/15/2013 7 days Matt 7/11/2013 7/13/2013 6 days Matt 7/10/2013 7/16/2013 6 days

• Edited by Wednesday, December 04, 2013 5:29 PM Amended question
Wednesday, December 04, 2013 5:27 PM

This should do it ;-)

```=INT(
CALCULATE(
LASTDATE(TimeStudy[Close Date]),
ALLEXCEPT('TimeStudy', TimeStudy[ID])
)
-
CALCULATE(
FIRSTDATE(TimeStudy[Open Date]),
ALLEXCEPT('TimeStudy', TimeStudy[ID])
)
)```

Notice how similar the newly added CALCULATE formula is; it behaves similarly to the LASTDATE version but this time it is looking for the first date in the [Open Date] column instead of the [Closed Date].

Wednesday, December 04, 2013 5:37 PM
• Yep....that's it.  Thank you!!!!

~UG1

Wednesday, December 04, 2013 6:34 PM