Forum Discussion

rogerdea's avatar
rogerdea
Helper IV
7 years ago
Solved

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

  • 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

  • Mariusz's avatar
    Mariusz
    Community Champion

    Hi rogerdea 


    Can explain the logic behind thous figures.

     

    Telford   5.4 days

    Bournemouth  8.1 days

    London  9.1 days

     

    Thanks

    Mariusz

    • rogerdea's avatar
      rogerdea
      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!