Unanswered

Sum Filter by last 24 hours

(0) Share

Report

Posted on by drobinson

I am trying to sum a total by the last 24 hours. I can't seem to get it to work. My sum work's as long as I remove the date parameter. I have tried several different ways.

The timestamp field in sql is formatted as [Timestamp] [datetime2](7) NOT NULL

Sum(Filter('Auditing.BatchTickets',PartCode=partvar,DateTimeValue(Timestamp,ShortDateTime)>= DateAdd( Now(), -24, Hours)),Quantity)

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sort by

WarrenBelz 156,373 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @drobinson ,

I am not an SQL user, but if the field is already date/time, you should be able to do this

With( { wLast: DateAdd( Now(), -24, Hours ) }, Sum( Filter( 'Auditing.BatchTickets', PartCode=partvar && Timestamp >= wLast ), Quantity ) )

if you need to convert it, use

DateValue(TimeStamp)

but you should not need ShortDateTime.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
WarrenBelz 156,373 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @drobinson ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
drobinson 23 on at

Like
a
(0)

Report
Copy link

Link copied!

did not work I get we cannot apply operator < to types datetimezone and datetime error.

Was this reply helpful? Yes No
drobinson 23 on at

Like
a
(0)

Report
Copy link

Link copied!

Still have not found a solution.

Was this reply helpful? Yes No
WarrenBelz 156,373 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I might tag @RandyHayes here - he may have an SQL solution.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@drobinson

I'd first recommend using a View in SQL to provide the results you want as it is a relatively static query.

If that is not an option, then you will need to create a calculated column in your table to represent the date as an integer and then perform the filter on that column. This method is mentioned in the SQL connector documentation as well.

I hope this is helpful for you.

Was this reply helpful? Yes No