Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I've seen a few topics on this but nothing specific to what i am trying to achieve.
What i am trying to do is, for each location in my data, measure the average number of days between dates for each entry by that location.
So in the sample data attached, I am trying to build a table visualisation which will list each location, and then give me the average days between each date. However, there can be duplicate days for each location (which is correct) but if this is the case then the duplicates can be ignored as it doesnt matter how many entries are the same date.
So I am aiming for something in a table that looks like:
Telford 5.4 days
Bournemouth 8.1 days
London 9.1 days
Does this make sense? The data is:
Thanks
Solved! Go to Solution.
@rogerdea Give this a try. You will need to change the name of your table in the measure.
Avg Days in Location = AVERAGEX ( VALUES ( 'Table'[Location] ), CALCULATE( DIVIDE ( DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY ), COUNTROWS ( 'Table' ) -1 ) ) )
Hi @rogerdea
Can explain the logic behind thous figures.
Telford 5.4 days
Bournemouth 8.1 days
London 9.1 days
Thanks
Mariusz
The figures were just an example not the expected results, i was just showing how the table would look, rather than the actual averages. I dont have the answer.. yet!
@rogerdea Can you give us the actual expected result for the data in your screenshot?
Sure i have just worked them out manually in excel and the results are:
Telford = 21
London = 26.6
Bournemouth = 30
Thanks
@rogerdea Give this a try. You will need to change the name of your table in the measure.
Avg Days in Location = AVERAGEX ( VALUES ( 'Table'[Location] ), CALCULATE( DIVIDE ( DATEDIFF ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ), DAY ), COUNTROWS ( 'Table' ) -1 ) ) )
Thank you its worked perfectly !
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |