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 da...
  • 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

     

     

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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Share your data and show the expected result.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi, here is the table. It has access dates (FECHA in spanish) of my clients 1 and 2. The first date is repeated with what gives me the error of the second image. Thanks!!!