cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffmorris1989 Frequent Visitor
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
Microsoft 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 ) )

 

average difference between multiple dates in column_1.jpg

 

Best Regards,
Herbert

 

 

View solution in original post

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

 

I appreciate your help,

 

Thanks,

Sean Super Contributor
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 Smiley Happy

 

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

 

 

Microsoft 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 ) )

 

average difference between multiple dates in column_1.jpg

 

Best Regards,
Herbert

 

 

View solution in original post

icastillo Frequent Visitor
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
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
https://www.linkedin.com/in/excelenthusiasts/
icastillo Frequent Visitor
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!!!

Sin título-1.jpgSin título-2.jpg

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)