Forum Discussion

jeffmorris1989's avatar
jeffmorris1989
Frequent Visitor
9 years ago
Solved

average difference between multiple dates in column

Hello,

 

I have a problem that I am probably overthinking, but what I need is a measure that will allow me to calculate the average difference between dates in a column. 

 

For example - if my date column looks like:

 

 4/14/2017

 4/15/2017

1/1/2017

 

I would like for this measure to return the average of days separating the values in the column.  Note that the column will not always be sorted, nor will there be a set number of dates in the column.

 

Thanks

 

Jeff

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    jeffmorris1989

     

    The calculation starts at first in column one by one, and then in the whole table. So I suggest you add a calculated column first.

     

    DaysBetween =
DATEDIFF (
    'Table'[date],
    FIRSTDATE (
        FILTER ( ALL ( 'Table'[DATE] ), 'Table'[DATE] > EARLIER ( 'Table'[DATE] ) )
    ),
    DAY
)

    And then you can create a measure and show the result in visual card.

     

    Measure =
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[DaysBetween] > 1 ),
    'Table'[DaysBetween]
)
/ SUMX ( 'Table', IF ( 'Table'[DaysBetween] > 1, 1, 0 ) )

     

     

    Best Regards,
    Herbert

     

     

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    jeffmorris1989

     

    The calculation starts at first in column one by one, and then in the whole table. So I suggest you add a calculated column first.

     

    DaysBetween =
DATEDIFF (
    'Table'[date],
    FIRSTDATE (
        FILTER ( ALL ( 'Table'[DATE] ), 'Table'[DATE] > EARLIER ( 'Table'[DATE] ) )
    ),
    DAY
)

    And then you can create a measure and show the result in visual card.

     

    Measure =
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[DaysBetween] > 1 ),
    'Table'[DaysBetween]
)
/ SUMX ( 'Table', IF ( 'Table'[DaysBetween] > 1, 1, 0 ) )

     

     

    Best Regards,
    Herbert

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello, the solution does not work when the first date is repeated. Any ideas?

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Its tough to answer without knowing the expected result...

    Does this Measure give you what you want?

    Avg Number of Days Between =
DIVIDE (
    DATEDIFF ( FIRSTDATE ( 'Table'[Date] ), LASTDATE ( 'Table'[Date] ), DAY ),
    DISTINCTCOUNT ( 'Table'[Date] ),
    0
)
    • jeffmorris1989's avatar
      jeffmorris1989
      Frequent Visitor
      9 years ago

      Hello Sean thanks for this response.  However I think something is missing. The result of the measure should be the average of the number of days between a list of dates.   

       

      If I have dates 1/1/2017, 1/2/2017 and 1/3/2017  the result should return 1.  

       

      I tested your mesaure on column of dates containing  1/14/17 and 3/11/2017 and the result is 28 days, when it really should be 56.  Maybe we should consider a -1 to the DistinctCount?

       

      I appreciate your help,

       

      Thanks,

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        jeffmorris1989

        Yes try something like this

        Avg Number of Days Between =
DIVIDE (
    DATEDIFF ( FIRSTDATE ( 'Table'[Date] ), LASTDATE ( 'Table'[Date] ), DAY ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Date] <> BLANK () )
    )
        - 1,
    0
)

        Distinctcount counts all blanks as 1 so the above will ignore any blanks you may or may not have

        Hopefully this resolves it :smileyhappy: