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

     

     

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!!!