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

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

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

    Thank you its worked perfectly !