Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am very new to BI and attempting some self paced learning. I have read through a few posts here to assiust with solving my problem but with no succes as yet.
Attempting to calculate time duration between rows of the same column and create a new "duration" column.
Picture attached for context.
Solved! Go to Solution.
If you want to do this in the query rather than as a DAX column you could use a technique like the following to join to the previous row https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...
It's not really clear from your question, but I'm guessing you just want to sort the times and calculation the difference from the current row to prior one. If so you could do something like the following to calculation the duration in minutes:
Duration Mins =
var _currentTime = Table1[Timestamp] var _previousTime = maxx( filter(VALUES(Table1[Timestamp]), Table1[Timestamp] < _currentTime), [Timestamp]) return if (_previousTime = 0, blank(), DATEDIFF( _previousTime, _currentTime, MINUTE))
Thanks @d_gosbell and apologies for the lack of clarity
I am attempting to create a new column in the querry editor that produces a time difference/duration time based on current row to the preceeding row of the Arrival Time column after the Arrival Time column has been sorted in Ascending order.
your solution allowed me to create a time difference column by creating a new column if modelling but I can not sort the Arrival Time in Ascending order as the arrival time is sorted in Date Hierarchy format Year/Quarter/Month/Day
hi, @Buster
Do you mean that you want to create it in edit queries? For you need to sort arrival time in Date Hierarchy format Year/Quarter/Month/Day in edit queries.
If so, just adjust the solution as below:
Step1:
After you sort arrival time in Date Hierarchy format Year/Quarter/Month/Day in edit queries, Then add an Index column.
Step2:
Then add a New column by this formula in modeling
Column = var _currentIndex = Table1[Index] var _previousIndex = maxx( filter(VALUES(Table1[Index]), Table1[Index] < _currentIndex), [Index]) return if (_previousIndex = 0, blank(), DATEDIFF( CALCULATE(SUM(Table1[arrival time]),FILTER(Table1,Table1[Index]=_previousIndex)), Table1[arrival time], MINUTE))
Regards,
Lin
If you want to do this in the query rather than as a DAX column you could use a technique like the following to join to the previous row https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |