Forum Discussion
calculate avg time - between multiple rows
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.
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
8) 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/
2 Replies
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
8) 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/
Thanks so much! that worked perfectly!
