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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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