cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Average days between dates and locations

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

1 ACCEPTED SOLUTION
Super User

@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
)
)
)```

6 REPLIES 6
Community Champion

Can explain the logic behind thous figures.

Telford   5.4 days

Bournemouth  8.1 days

London  9.1 days

Thanks

Mariusz

Helper IV

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!

Super User

@rogerdea Can you give us the actual expected result for the data in your screenshot?

Helper IV

Sure i have just worked them out manually in excel and the results are:

Telford = 21

London = 26.6

Bournemouth = 30

Thanks

Super User

@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
)
)
)```

Helper IV

Thank you its worked perfectly !

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors