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

Having problems creating a report/chart with totals grouped by month

I have a very large table that has 100s of thousands of records.  I am trying to populate a data table and then ultimately a line chart that will total the number of records with a given code by month for a given date range.  I think that I am having delegation problems because of the size of the tables, also not sure the best way to get the date which is in date time format into a YYYY/MM format.

 

Table looks like this:  'dbo.mytable'  (extremely large table)

 

DateProduct ID
2/23/17 12:00 AM100
2/24/17 12:00 AM200
3/25/18 12:00 AM100
2/26/19 12:00 AM100
2/27/19 12:00 AM100
3/28/19 12:00 AM200
3/29/19 12:00 AM100
1/1/20 12:00 AM100
1/2/20 12:00 AM200
1/3/20 12:00 AM200
1/4/20 12:00 AM100
2/3/20 12:00 AM200
2/4/20 12:00 AM100
2/5/20 12:00 AM100
2/6/20 12:00 AM200

 

So if wanted to see monthly totals for all data > 2019 for product "100" and have it look like this:

 

Month Total
2/192
3/191
1/202
2/202

 

Thanks so much.

1 REPLY 1
Highlighted
Memorable Member
Memorable Member

Hi @nhale ,

Please consider to create a view in your SQL database to structure your data as you need having in mind also a  column for filtering data in PowerApps (in this case a column of INT type -  YYYYMM let's call it YearMonthInt is what you need).

Connect in PowerApps app  to this view and then use a gallery with Items property set to:

Filter ('yourView', YourMonthlyLimitAsYYYYMM<YearMonthInt)

 

Hope it helps !

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

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