calculate avg time - between multiple rows

[kymilue]

I need to calculate the avg time (hours) between scans for each ID. I have a set of data where there is an index, an ID number and a scan timestamp. Some IDs have been scanned only once, others more than once, and the index # is not consecutive. I've created a measure that counts the rows, but struggling to calculate the difference between rows to then calculate the average. 

[Datatouille · Accepted solution]

Hi @kymilue

It is quite tricky to cope with the concept of previous Row with pure DAX solutions.

I Suggest you use Power Query first !

From Power Query (Query Editor in Power Bi Desktop), 

1) Sort in ascending order by IDs (first) and by Scan Ts (second)

2) Create 2 custom columns with Power query: Index Column From 0 and Index Column from 1

3) Left join the table with itself by using Index and Index.1 as matching keys 1  // ID and ID as matching keys 2 (Remain Ctrl)

4) Develop and only keep the Scan.ts element

5) Create a new custom column in Power Query: ScanTs - Scan Ts.1

6) Format this new custom column as decimal and rename it TimeDifference

7) Close and Load the Query

😎 Create the following measure: AvgTime = Average(YourTable[TimeDifference])

And enjoy 🙂

What this awesome video from @MattAllington to see the different steps and understand this pattern: http://exceleratorbi.com.au/use-power-query-compare-database-records/

[kymilue]

Thanks so much! that worked perfectly!