Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Hello, the solution does not work when the first date is repeated. Any ideas?
Hi,
Share your data and show the expected result.
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!!!
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,
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
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
User | Count |
---|---|
114 | |
106 | |
83 | |
67 | |
42 |
User | Count |
---|---|
157 | |
110 | |
84 | |
80 | |
60 |