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
secondImage

New Return to Workplace

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

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,446)