cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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
Highlighted
Power Apps
Power Apps

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
Highlighted
Power Apps
Power Apps

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.

Highlighted
Power Apps
Power Apps

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)
Highlighted
Regular Visitor

Re: Help with "sumif" and days between dates

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

Highlighted
Regular Visitor

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.

Highlighted
Power Apps
Power Apps

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,978)