cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IRNPH
New Member

SUMIFS AND MONTH FUNCTION

Hey guys, been having a hard time finding answers.

I have a datatable on my powerapps and i just want the sum of the amount based on 2 columns - ID code and Month which should be the same as the current month.

  1. i dont know how to write or code the sumifs
  2. and use Month on the column (date) to match the current date

 

This is the formula i made:

 

If(IsBlank(TextInput3_Searchbox)," ",1000-Sum(Filter('Employee Discount | Discount Data', TextInput3_Searchbox.Text= 'Employee Code'&& Month('Transaction Date') = Month(Today()),Discount)

 

HELP!!!!!!!!!!

10 REPLIES 10
PG_WorXz10
Community Champion
Community Champion

Hi @IRNPH ,

 

Assuming your logic is fine. Does below code works in your case ?

 

If(IsBlank(TextInput3_Searchbox)
," ",
1000-Sum(Filter('Employee Discount | Discount Data', TextInput3_Searchbox.Text= 'Employee Code'&& Month('Transaction Date') = Month(Today()),Discount)))

 

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.

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Sadly does not 😞

PG_WorXz10
Community Champion
Community Champion

Hi @IRNPH ,

 

What is the error message you getting ?

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Hi, it just says error in formula, please try revising...

WarrenBelz
Super User
Super User

Hi @IRNPH ,

Your two possible outcomes are different types (Text and Numeric) and you also had a bracket in the wrong place - try this

If(
   IsBlank(TextInput3_Searchbox),
   0,
   1000-
   Sum(
      Filter(
         'Employee Discount | Discount Data', 
         'Employee Code' = TextInput3_Searchbox.Text && 
         Month('Transaction Date') = Month(Today())
      ),
      Discount
   )
)

 

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.

Hi, i used it and there isn't any error showing.. but it doesn't recognize the Transaction date (shown on the table as "Saturday, August 21, 2021" as August I guess? because it still showing "1000"

IRNPH_0-1630374254481.png

 

@IRNPH ,

What type of field is 'Transaction Date' and what is your Data Source?

Hi @WarrenBelz its sharepoint and its a date

 

Hi @IRNPH ,

There is something else wrong here - if 'Transaction Date' is a date field in SharePoint, then 

 Month('Transaction Date') = Month(Today())

will show all the records with the current month with one proviso - Month() is not Delegable, so you will not get all the records on large data sets - how big is you list?

Also what code are you using to display the date the way you have it in the screenshot?

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,575)