Using With messes up a Filter by Date?

(0) Share

Report

Posted on by Medoomi

523

I am using a filter to extract records from a Dataverse Table and then to Sum the column 'Proj Value ($)'

When I use the following formula, it works correctly:

Text(
 Sum(
 Filter(
 Projects,
 'OP Status Change Date' >= Date(
 Year(Today()),
 1,
 1
 ),
 'OP Status Change Date' <= DateAdd(
 Date(
 Year(Today()),
 1,
 1
 ),
 1,
 Years
 ),
 'OU and Salesperson'.'OU Region' = galManagers.Selected.Region,
 'Opportunity Status'.'Dashboard Sold' = "yes"
 ),
 'Proj. Value ($)'
 ),
 "[$-en-US]$#,###.00"
)

When I use With in the formula, it retrieves an incorrect value:

With(
 {
 StartDate: Date(
 Year(Today()),
 1,
 1
 ),
 EndDate: DateAdd(
 Date(
 Year(Today()),
 1,
 1
 ),
 1,
 Years
 )
 },
 Text(
 Sum(
 Filter(
 Projects,
 'OP Status Change Date' >= StartDate,
 'OP Status Change Date' <= EndDate,
 'OU and Salesperson'.'OU Region' = galManagers.Selected.Region,
 'Opportunity Status'.'Dashboard Sold' = "yes"
 ),
 'Proj. Value ($)'
 ),
 "[$-en-US]$#,###.00"
 )
)

Can anyone see where I am going wrong in the use of With?

Thanks!

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Are you getting an error message? as I have mimicked what you have done with the With() to a basic level and had no errors i didn't combine it with the sum and filter but I can try that as well if we have no errors to point us in the right direction to begin with 🙂

edit: i tried to do it with the sum and a filter and got blank values. The with variables were not blank but the Sum was Blank. When made the dates into context variables set OnVisible of Screen and used them instead of With it worked. Just experimenting to see why 🙂
Medoomi 523 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @AJ_Z ,
I receive a delegation warning on the first filter that it might not work correctly on column 'OP Status Change Date', but that's the only error I receive. The Table only has 270 records in it, so I'm not concerned just yet with the delegation warning. It's the miscalculation I'm worried about.
As far as I can tell, there's no difference in the formula, however the value amounts calculated by the two formulas are substantially different...
Medoomi 523 on at

Like
a
(1)

Report
Copy link

Link copied!
Hmmm, so I moved the date calculations to OnVisible like so:

UpdateContext( { varYearStartDate: Date( Year(Today()), 1, 1 ) } ); UpdateContext( { varYearEndDate: DateAdd( Date( Year(Today()), 1, 1 ), 1, Years ) } )

And changed the formula to read:

Text( Sum( Filter( Projects, 'OP Status Change Date' >= varYearStartDate, 'OP Status Change Date' <= varYearEndDate, 'OU and Salesperson'.'OU Region' = galManagers.Selected.Region, 'Opportunity Status'.'Dashboard Sold' = "yes" ), 'Proj. Value ($)' ), "[$-en-US]$#,###.00" )

And now it correctly sums the value.
Was I just expecting PowerApps to calculate too many values within the formula?
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@Medoomi

Yes...this is an issue with the With and certain Data types with a filter. PowerApps tries to always convert the formula you provide into a filter statement against the session table data. Sometimes it screws up!! This is one of those scenarios where it gets it wrong. You'll notice that the main goal of the formula you have is to avoid delegation and yet...the formula editor will still state that there is a delegation warning. So that's the first clue that it is not getting it right.

If you use the following formula, it should give you better results:

With( { StartDate: Date(Year(Today()), 1, 1), EndDate: DateAdd(Date(Year(Today()), 1, 1), 1, Years) _preFilter: Filter(Projects, 'OU and Salesperson'.'OU Region' = galManagers.Selected.Region, 'Opportunity Status'.'Dashboard Sold' = "yes" ) }, Text( Coalesce( Sum( Filter( _preFilter, 'OP Status Change Date' >= StartDate, 'OP Status Change Date' <= EndDate ), 'Proj. Value ($)' ), 0 ), "[$-en-US]$#,###.00" ) )

The above will give you the correct results and it will not get confused.

The trick is to prefilter the data by other delegable criteria, and then perform the remaining against the results.

If the prefilter is returning more than the maximum record limit, then you can also perform the following:

With( { StartDate: Date(Year(Today()), 1, 1), EndDate: DateAdd(Date(Year(Today()), 1, 1), 1, Years) _preFilter: Filter(Projects, 'OP Status Change Date' >= Date(Year(Today()), 1, 1), 'OU and Salesperson'.'OU Region' = galManagers.Selected.Region, 'Opportunity Status'.'Dashboard Sold' = "yes" ) }, Text( Coalesce( Sum( Filter( _preFilter, 'OP Status Change Date' >= StartDate, 'OP Status Change Date' <= EndDate ), 'Proj. Value ($)' ), 0 ), "[$-en-US]$#,###.00" ) )

Basically, the more you move forward with it, you eventually see that you don't even need the start and end dates to be With variables.
Medoomi 523 on at

Like
a
(2)

Report
Copy link

Link copied!

Perfect, thank you so very much--it's definitely a learning experience 🙂
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Awesome thanks for sharing this Randy i was unaware of this solution to the with issue I usually work around it by using UpdateContext and Set to define variables elsewhere and reference them in formulas where i face this and this is not ideal for every situation. I will take note of this and keep it in mind for the future 🙂

- AJ
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@AJ_Z

Yes, but maintaining variables is a pain! 😫 I avoid variables at all cost for that reason (and others). But the With statement (even though it is a variable) keeps it all in one place. Plus it doesn't need a behavioral action to use!

However, the key is that the formula editor tries to figure out what you are doing and apply its own path to get the results. That is where the issue comes in. If you actually watch the formula that gets built (it would be a REST call) in the Monitor Tool, you'll see the kind of logic (or you can deduce what kind) the app tries to put together. Sometimes it's very interesting!