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

     

     

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:

 

  • jeffmorris1989's avatar
    jeffmorris1989
    Frequent Visitor
    9 years ago

    Thanks for the reply Sean.  I believe I did a horrible job explaning this problem - sorry!

     

    Lets pretend this date column is for the dates that an employee works:

     

     4/1 

     4/2

     4/3

     

    That would be 3 dates that the employee has worked, each with 0 days in between off. Now what if their date column looks like:

     

     4/1

     4/3

    4/5

     

    That would be 3 days that the employee has worked, each with 1 day off; an aveage of 1 day off would be the measure's result.  

     

    What I would like the measure to do, essentially, is to tell me the average number of days off for an employee over the date column.   I think the problem I am running into is that doing a Last  - First / Distinct Count of Dates assumes that each date within the Dates column is spread out evenly. 

     

    I think what I need is an interative measure that then takes an average;  4/3 - 4/1  = 1 day off;  4/5 - 4/3 = 1 day off;  1 + 1/  2