cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NigelC
Level: Powered On

Help with "sumif" and days between dates

I have two separate questions that I'm hoping someone can help with? 

 

I've created a PowerApp, based on a list of sales - the app enables data entry of each sale and also has an analysis page, I have two date pickers on the analysis page and I'm trying to display the volume of sales, other sales data such as volumes of individual colours sold and also the value of sales recorded, within the date range entered.

 

For counting colours sold and volume of sales, I'm simply using a countif, using the date sold and the required colour as arguments to the countif. I'm stuck on adding the value of the sales, as there is no equivalent sumif. Any ideas?

 

Additionally, my countif is working fine, using the syntax Countif(Table, Date_Sold>=DatePicker1, Date_Sold <=DatePicker2). However, I'm trying to display the number of days between dates and use that value to display average sales value per day (within the date range). I get wild values if I enter DatePicker2-DatePicker1. Using DateDiff(DatePicker1, DatePicker2) gives me an error, both as typed and as using DateValue(DatePicker...). Can someone tell me what I'm doing wrong?

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Help with "sumif" and days between dates

You should first filter the table, then apply the Sum function to it:

Text(
    Sum(
        Filter(
            Table2,
            Date_x0020_Sold > DatePicker1_1.SelectedDate - 1,
            Date_x0020_Sold < DatePicker1.SelectedDate + 1),
        Sale_x0020_Price),
    "[$-en-GB]£##.00")

Notice that you can use multiple conditions in the Filter function, and it will filter the items that satisfy all conditions.

View solution in original post

5 REPLIES 5
PowerApps Staff AndyPennell
PowerApps Staff

Re: Help with "sumif" and days between dates

For the second problem you need to use DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate). Given this, I am surprised your CountIf formula works.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Help with "sumif" and days between dates

Try getting the SelectedDate property of the date picker to retrieve the actual date:

DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days)
NigelC
Level: Powered On

Re: Help with "sumif" and days between dates

Thank you Andy & Carlos, that has sorted my DatePicker issue.

NigelC
Level: Powered On

Re: Help with "sumif" and days between dates

In relation to my "sumif" query, I've entered:

Text(Sum(If(Table2.Date_x0020_Sold>(DatePicker1_1.SelectedDate-1),If(Table2.Date_x0020_Sold<(DatePicker1.SelectedDate+1),Table2.Sale_x0020_Price,0),0), "[$-en-GB]£##.00")

Where Table2 is my data source, Date_x0020_Sold & Sale_x0020_Price are columns in the table.

But I receive the error message "Warning: This predicate is a literal value and does not reference the input table". So I take it that this formula is acting on one record and not against the entire table?

 

Thanks.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Help with "sumif" and days between dates

You should first filter the table, then apply the Sum function to it:

Text(
    Sum(
        Filter(
            Table2,
            Date_x0020_Sold > DatePicker1_1.SelectedDate - 1,
            Date_x0020_Sold < DatePicker1.SelectedDate + 1),
        Sale_x0020_Price),
    "[$-en-GB]£##.00")

Notice that you can use multiple conditions in the Filter function, and it will filter the items that satisfy all conditions.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,331)