Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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/
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!
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |