cancel
Showing results for
Did you mean:
jeffmorris1989 Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions v-haibl-msft
Microsoft

## Re: average difference between multiple dates in column

@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 8
Sean Super Contributor

## Re: average difference between multiple dates in column

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 Frequent Visitor

## Re: average difference between multiple dates in column

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?

Thanks,

Sean Super Contributor

## Re: average difference between multiple dates in column

@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 jeffmorris1989 Frequent Visitor

## Re: average difference between multiple dates in column

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 v-haibl-msft
Microsoft

## Re: average difference between multiple dates in column

@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

icastillo Frequent Visitor

## Re: average difference between multiple dates in column

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

## Re: average difference between multiple dates in column

Hi,

Share your data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
icastillo Frequent Visitor

## Re: average difference between multiple dates in column

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!!!  Announcements #### New Topics Started Badges Coming  