Answered

How to calculate a sum of multiple date/time value differences?

(0) Share

Report

Posted on by ramzez

312

I am creating a timestamp application where employees log when they start working, when they start a break and when they leave work.

How can I sum the time differences between different timestamps? I'm trying to use ForAll and DateDiff but cannot get it working. From the picture, BREAK duration should be 45min and POSITION 1 duration should be 90 minutes. The table will have thousands of rows once in use.

This is what the essential data looks like.

Screenshot 2023-02-17 at 14.41.11.png

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ramzez ,
Does this answer help you at all?
Solved: calculate sum of hours against multiple datetimes - Power Platform Community (microsoft.com)
If not, perhaps you can share a bit more about the expression you've tried, and what exactly isn't working?

Kind regards,
RT
ramzez 312 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for the reply!

I'm familiar with that thread and have used it as reference. This is what I have at the moment, it is not calculating correctly.

Sum( ForAll( Sequence(CountRows(Filter(colTimes, worktype = "BREAK"))), If(Value < CountRows(colTimes), DateDiff( // start time Index( Sort( colTimes, time, Descending ),Value+1 ).time, // end time Index( Sort( colTimes, time, Descending ), Value ).time, Minutes ) ) ), Value )
RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @ramzez ,
I'm not sure why you're filtering by break to get a sequence subset instead of just working through the whole table.... I'm also not sure if you're wanting to see the deltas somewhere or just sum them into a label.
For now, let's assume we want to check the delta for every row and its preceding row, then you can fiddle with the calc to suit your needs.

First of all, your calculations look right, but you're not putting the DateDiff outputs anywhere that you can sum them, and you're summing the sequence value instead.

Ignoring the filter for now, if I try to take your original meaning and apply what I suspect the outcome should be, then a text label Text: property would look something like this to sum all the time deltas;
Sum( AddColumns( Sequence(CountRows(colTimes)), //as many rows as are in table "Delta", DateDiff( // start time Index( Sort(colTimes,time,Descending), Value+1).time, //preceding row time // end time Index( Sort(colTimes,time,Descending), Value).time, //current row time Minutes ) ), Delta)
Hope this helps,
RT
ramzez 312 on at

Like
a
(0)

Report
Copy link

Link copied!
I'm filtering with "BREAK" because I'm trying to calculate how much the user has been on a break.

The code you provided is giving an error that the index has to be within the lower and upper bounds of the table.

With this, I'm able to calculate the BREAK time, but it does not work with "POSITION 1". I sorted the record in the colTimes collection to simplify this.
With( { _timestamp: "BREAK" }, Sum( ForAll( If( Last(colTimes).worktype = _timestamp, Sequence(CountRows(Filter(colTimes, worktype = _timestamp))-1), Sequence(CountRows(Filter(colTimes, worktype = _timestamp))) ), If(Value < CountRows(colTimes), DateDiff( // start time Index( colTimes, Value+1 ).time, // end time Index( colTimes, Value ).time, Minutes ) ) ), Value ) )
Verified answer

ramzez 312 on at

Like
a
(1)

Report
Copy link

Link copied!
I think I cracked it, this seems to work:

With( { _filteredTimestamps: Filter(colTimes, worktype = "BREAK") }, Sum( ForAll( Sequence(CountRows(_filteredTimestamps)), If(Value < CountRows(colTimes), DateDiff( Index(_filteredTimestamps,Value).time, LookUp(colTimes, RowNumber = Index(_filteredTimestamps, Value).RowNumber - 1).time, Minutes ) ) ), Value ) )

I added row numbers to colTimes so I could reference it.