cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bub
Helper I
Helper I

Date Range Data Lookup

Greetings Everyone,

 

Another day, another learning curve within PowerApps. I've spent a few hours looking into today's problem and I'm not even sure how to describe it correctly so I'm hoping someone clever can help me a bit ...

 

I have a very large excel spreadsheet which gets updated daily with 2 lines of information for a few dozen sites. I need to be able to sum the data for a specific site from a specific column in the data for a range of dates via a date picker.

 

The data, in excel, looks something like this:

 

Bub_2-1624943885018.png

 

I want to be able to select a site (each has a unique name and site code) and select a date from a picker in the past then sum the Paint and Coating lines for that site within that date range.

 

Example: date selected = 23/06/2021 and site = DERBY gives Paint = 1613 (765+213+635) and Coating = 1661 (222+876+563).

 

The source spreadsheet may extend to 300,000 lines at some point & I'm aware that this isn't really the best way to handle such a large dataset; I'm doing it in-order to learn PowerApps mostly.

 

It seems straightforwards but I don't know where to start in PowerApps with this.

 

Is there a solution? Thanks for looking & have a great day whatever you're doing.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @Bub ,

 

I did a simple demo for you.

1\ This is my excel table 'Table1' in onedrive for bussiness.

vxiaochenmsft_0-1624952249324.png

 

2\ Add a datepicker control 'DatePicker1'.

 

3\ Add a label control and set its Text property to:

Sum(Filter(Table1,Address="DERBY",DateValue('Sales Date',"bg-BG")>=DatePicker1.SelectedDate,FAMILY="Paint"),FRODS)

 

4\ Add a label control and set its Text property to:

Sum(Filter(Table1,Address="DERBY",DateValue('Sales Date',"bg-BG")>=DatePicker1.SelectedDate,FAMILY="Coating"),GRODS)

 

5\ The result is as follows:

vxiaochenmsft_1-1624952377635.png

 

In addition, the excel table has a delegation limitation.

When the number of your excel records exceeds 2000, powerapps will not be able to process.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

Hi @Bub ,

 

I did a simple demo for you.

1\ This is my excel table 'Table1' in onedrive for bussiness.

vxiaochenmsft_0-1624952249324.png

 

2\ Add a datepicker control 'DatePicker1'.

 

3\ Add a label control and set its Text property to:

Sum(Filter(Table1,Address="DERBY",DateValue('Sales Date',"bg-BG")>=DatePicker1.SelectedDate,FAMILY="Paint"),FRODS)

 

4\ Add a label control and set its Text property to:

Sum(Filter(Table1,Address="DERBY",DateValue('Sales Date',"bg-BG")>=DatePicker1.SelectedDate,FAMILY="Coating"),GRODS)

 

5\ The result is as follows:

vxiaochenmsft_1-1624952377635.png

 

In addition, the excel table has a delegation limitation.

When the number of your excel records exceeds 2000, powerapps will not be able to process.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

Thank you. That's really helpful.

 

Is there a work around for more than 2000 rows though? I need a lot more than that. 

v-xiaochen-msft
Community Support
Community Support

Hi @Bub ,

 

If you only use Powerapps and your data source is excel table, then this is difficult to achieve.

You could use powerapps to trigger flow to get the data in the table and then return it to powerapps.

However, its upper limit is only 100,000 records.

This has not reached your data volume.

vxiaochenmsft_2-1624953561029.png

Even if you use filter query to make the amount of data less than its upper limit, its performance will be very bad.

Your flow will run for a long time.

 

In other words, powerapps is not very suitable for handling huge amounts of data.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

Thank you @v-xiaochen-msft : we have the data in a database somewhere so I guess a query to that instead would be a more efficient solution. You're right, I think it would take ages to run with so many rows. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,430)