none
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 undergrads1 Sunday, November 24, 2013 3:48 PM
    Sunday, November 24, 2013 3:48 PM

Answers

  • Hi undergrads1,

    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
    Moderator

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)

    Answer an interesting question? Create a wiki article about it!

    Friday, November 29, 2013 11:22 PM
    Owner
  • Hi Undergrads1,

    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
    Moderator
  • Michael (or anyone else),

    Your answer above worked.  Thank you!

    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 undergrads1 Wednesday, December 04, 2013 5:29 PM Amended question
    Wednesday, December 04, 2013 5:27 PM
  • Hi undergrads1,

    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
    Moderator
  • Yep....that's it.  Thank you!!!!

    ~UG1

    Wednesday, December 04, 2013 6:34 PM