Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
v-haibl-msft
Employee
Employee

@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
v-haibl-msft
Employee
Employee

@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

 

 

Anonymous
Not applicable

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

Hi,

 

Share your data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

 

Sean
Community Champion
Community Champion

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
)

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,

@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

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.