Forum Discussion

rogerdea's avatar
rogerdea
Icon for Helper IV rankHelper 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 e...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

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

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

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
Icon for Helper IV rankHelper IV
7 years ago

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!

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

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

    • rogerdea's avatar
      rogerdea
      Icon for Helper IV rankHelper IV
      7 years ago

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

       

      Telford = 21

      London = 26.6

      Bournemouth = 30

       

       

      Thanks

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

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