Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kymilue
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

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/

View solution in original post

2 REPLIES 2

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/

Thanks so much! that worked perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.