cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kymilue Frequent Visitor
Frequent Visitor

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. 

 

timebetweenscans.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Excelside Established Member
Established Member

Re: calculate avg time - between multiple rows

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 Smiley Happy

 

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 2
Highlighted
Excelside Established Member
Established Member

Re: calculate avg time - between multiple rows

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 Smiley Happy

 

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 Frequent Visitor
Frequent Visitor

Re: calculate avg time - between multiple rows

Thanks so much! that worked perfectly!