Help with formula to filter field by the due date month

[tscott07]

Text(Sum(Filter('Estimating List', Text(DueDate,"[$-en-US]m-yy") = Text(Now(), "[$-en-US]m-yy")), EstContractValue)), "$#,###0.00"))

Here is my formula. It currently does not error but i do not get a result either. total should be: $2,650,000

the sum of the estcontractvalues field is 29188048 then when filtered by the month of june it should get 2650000.

I am not sure what the issue is. Can some help? 

[mdevaney]

@tscott07 

Are you getting a delegation warning? The formula is. It delegation-friendly.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[tscott07]

yes, the filter may not work on large datasets. 

my sharepoint list currently only has 25 items. 

@mdevaney 

[KrishnaV]

Hi @tscott07 ,

Change you formula as below:

Text(Sum(Filter('Estimating List',Text(DueDate,"[$-en-US]m-yy") = Text(Now(), "[$-en-US]m-yy")), Value(EstContractValue)), "[$-en-US]$#,###0.00")

 
I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[tscott07]

i changed the due date to m/d/yyyy and added value and no change. 

Text(Sum(Filter('Estimating List', Text(DueDate,"[$-en-US]m/d/yyyy") = Text(Now(), "[$-en-US]m/d/yyyy")), Value(EstContractValue)), "[$-en-US]$#,###0.00"))

[KrishnaV]

Hi @tscott07 ,

if you still see the issue in sum validate that how many rows you are getting as part of the filter with the below process:

if you are not getting all the rows from the list ensure that your row limit is min of 500 at the app setting level:

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[tscott07]

I am getting the rows in the list when i do filter('Estimating List' but now i see that the time is coming in, with the due date. 

is that making a difference? 

2 date column is duedate

[KrishnaV]

Hi @tscott07 ,

Nope, that shouldn't stop you, please go ahead and complete the filter and then add SUM function on top of that.

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[tscott07]

I added the sum and filter and get a return. Yes! I get the total in the formula display but it will display in the field???

The filtered total for June is now $3,150,000.00. Any ideas why it is not displaying? No errors still just the delegation warning.

[KrishnaV]

Hi @tscott07 ,

Ok one by one:
1. I am very happy that the SUM is working as expected.

2. How are you setting the value to that label? onSelect of any button or OnVisible of screen?

3. If it is OnSelect property it will only get populate in Play mode.

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.