web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : HFKAU8htwRDqD3tAUTiaLy
Power Apps - Building Power Apps
Answered

How to count and group by month and year from SharePoint list and create a collection to use in a chart?

Like (0) ShareShare
ReportReport
Posted on 4 Aug 2021 13:50:54 by 514

I want to use the chart facility in PowerApps.

 

I would like to show the months of the year and a total of completed items in each month.

 

My SharePoint list has columns for CompletedDate, CompletedDateMonth, CompletedDateYear

I am unsure if I need to create a months collection with the data and then base the chart on this data source.

Is there a way of grouping and counting and mapping the month name to a month name in the collection?

 

Jan Feb March

2     3      7

 

etc


Any example appreciated

 

Thanks

 

 

Categories:
  • Community Power Platform Member Profile Picture
    on 10 Aug 2023 at 23:52:44
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Disregard, I figured it out . . .

     

    SortByColumns(
     With(
     {
     _items: AddColumns(
     Filter(
     myList,
     myDateColumn >= Dropdown2.Selected.Start && examDate <= Dropdown2.Selected.End
     ),
     "_month",
     Month(myDateColumn)
     ),
     startMonth: 10
     },
     ForAll(
     Sequence(12),
     {
     Month: Last(
     FirstN(
     Calendar.MonthsShort(),
     Value
     )
     ).Value,
     Count: CountRows(
     Filter(
     _items,
     _month = Value
     )
     ),
     MonthOrder: If(
     Value < startMonth,
     13 + (Value - startMonth),
     Value - (startMonth-1)
     )
     }
     )
     ),
     "MonthOrder"
    )
  • Community Power Platform Member Profile Picture
    on 10 Aug 2023 at 23:05:50
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Hi Randy.  I know this is really old post.  I'm using your solution for the chart and everything works.  However, I need the chart to be by fiscal year (start 10/1/lastYear, end 9/30/thisYear).  I changed the start and end dates in the dropdown and the table looks okay.  I can't figure out how to get the x-radius of months to start at October of the previous year.  I tried Sequence(12, 10) but it only lists until Dec and then Dec for all the remaining months.

     

    Any thoughts?

  • iwonder Profile Picture
    1,142 on 27 Jan 2022 at 14:59:53
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Hi @RandyHayes 

     

    I just realized that my CountRows() is not working as expected... and I think I may know why...

     

    It seems if I make the date range too wide, my counts per month are incorrect. Narrowing my date range makes my counts correct. The wider I make it, the more incorrect the value is

     

    Ex: if the correct value is 56 and my date range is a few months. I add a few more months and my count is now 49... more months 41... more months 38.... and so on

     

    Is there a limit of the amount of data like the 2000 records thing?

     

    EDIT: Did more testing and it seem to be a 500 record limit...

    I am using Dataverse tables.

     

    Thanks

     

     

     

     

  • RandyHayes Profile Picture
    76,287 Super User 2024 Season 1 on 26 Jan 2022 at 23:26:24
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    @iwonder 

    Have a great evening as well!! 

  • iwonder Profile Picture
    1,142 on 26 Jan 2022 at 23:16:53
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Brilliant!!! Thank you so much @RandyHayes 

     

    have a super evening

  • RandyHayes Profile Picture
    76,287 Super User 2024 Season 1 on 26 Jan 2022 at 23:04:06
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    @iwonder 

    Doing a GroupBy on a single series is the same as a Distinct.  Think about GroupBy as handing someone a pile of paper with numbers on the top.  Six papers with 2, 2, 3, 2, 4, and 5.

    You ask them to group them together for you.  They hand you back 4 piles in bins.  One bin has a paper with a number 2 on it.  You look closer and see that there are 3 papers with the number 2 in that bin.  The next bin you look and see a paper with the number 3 on it.  You look closer and see only 1 paper.  Same for 4 and 5.

    And...if you want to know how many papers have 2 on them, well, you'd just count the number of pages in the bin for number 2.

     

    SO...When you use GroupBy, you are saying - take all these papers (records in a table) (first parameter to GroupBy) and group them by "x" (where x is the column or identification like in the case of the paper) that you want to separate them by (second parameter to groupby).  And then, put them in a bin (last parameter to groupby).  

     

    The GroupBy function will return a table (unlike the person with the bins).  Each row in that table will have, the column value that you grouped by and a "bin" (table column) that will have all the records that match that column value.

     

    So, if we take the paper to records...

       GroupBy(paperStack, "NumberAtTopOfPaper", "bin")

    This will take the paper stack, put all the records together that have the same "NumberAtTopOfPaper" and then return them in the "bin" table.

    And, if we want to know how many there are, we just CountRows on the bin column.

     

    Another way to look at it, in a semi-PowerApps way is, this formula is equivalent to the GroupBy:

    ForAll(
     Distinct(paperStack, NumberAtTopOfPaper),
     {NumberAtTopOfPaper: Result,
     bin: Filter(paperStack, NumberAtTopOfPaper = Result)
     }
    )

    The above would return a table with a column called NumberAtTopOfPaper that would be all the distinct values and a column called bin, the would be all the records that have that number value.

     

    SO...GroupBy does all of the above for you in one statement.

     

    Now, there is more to GroupBy than just the above simple example, but for what you are doing, it is sufficient.

     

    Hopefully it is clear and understandable.

  • iwonder Profile Picture
    1,142 on 26 Jan 2022 at 22:05:43
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Thank you @RandyHayes 

     

    I thought GroupBy would return one value per group of values

     

    So if I grouped the following

    2,2,3,2,4,5

    I'd get 2,3,4,5 So I was not sure how I could count the single values... but I guess the GroupBy actually has

    2,2,2

    3

    4

    5

     

    Is that accurate?

     

    Thanks

  • RandyHayes Profile Picture
    76,287 Super User 2024 Season 1 on 26 Jan 2022 at 21:12:08
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    @iwonder 

    The GroupBy has already done all of the filtering for you.  Doing another filter in your countrows is just going to be more data interaction while ignoring what you've already gotten.

    So, the formula should be:

    With(
     {
     _Data: Sort(
     AddColumns(
     Filter(
     Assets,
     WarrantyExpireDate >= DateValue(WarrLSStart) && WarrantyExpireDate <= DateValue(WarrLSEnd)
     ),
     "_YearMonth",
     Text(WarrantyExpireDate,"yyyy") & Text(
     WarrantyExpireDate,
     "mm"
     )
     )._YearMonth,
     _YearMonth,
     Ascending
     )
     },
     ForAll(
     GroupBy(
     _Data,
     "_YearMonth",
     "_gYearMonth"
     ),
     {
     _rYearMonth: _YearMonth,
     _rCount: CountRows(_gYearMonth)
     }
     )
    )

    In other words, you did the group by based on the YearMonth, and you put all the records that match that into the _gYearMonth column...so there is no need to re-filter by that yearMonth as you had already done it in the GroupBy - so just count those rows.

  • iwonder Profile Picture
    1,142 on 26 Jan 2022 at 20:13:10
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Hi @RandyHayes 

     

    I was able to get this to run and return data however each of my counts = 500 and I don't know why

     

    With(
     {
     _Data: Sort(
     AddColumns(
     Filter(
     Assets,
     WarrantyExpireDate >= DateValue(WarrLSStart) && WarrantyExpireDate <= DateValue(WarrLSEnd)
     ),
     "_YearMonth",
     Text(WarrantyExpireDate,"yyyy") & Text(
     WarrantyExpireDate,
     "mm"
     )
     )._YearMonth,
     _YearMonth,
     Ascending
     )
     },
     ForAll(
     GroupBy(
     _Data,
     "_YearMonth",
     "_gYearMonth"
     ),
     {
     _rYearMonth: _YearMonth,
     _rCount: CountRows(
     Filter(
     _Data,
     _YearMonth = _YearMonth
     )
     )
     }
     )
    )
  • iwonder Profile Picture
    1,142 on 26 Jan 2022 at 17:25:47
    Re: How to count and group by month and year from SharePoint list and create a collection to use in a chart?

    Hi @RandyHayes 

     

    Found another of your great posts!

     

    I am trying to do something very similar, however I have 2 sliders on a different screen that allows a user to select a Start and End YearMonth [yyyymm]   and a a button that uses Set() to convert the yyyymm values selected by the user to the first day of the start month to the last day of the end month and then sends the user to a screen with a column chart where I want to show CountRows() per yyyymm and of course sort them from left to right.

     

    Button OnSelect: 

    Set(WarrLSStart,Date(Left(WarrantyLSStart,4),Mid(WarrantyLSStart,5,2),1));
    Set(WarrLSEnd,Date(Left(WarrantyLSEnd,4),Value(Mid(WarrantyLSEnd,5,2))+1,0));
    Navigate(Screen1,ScreenTransition.UnCoverRight)

     

    I'm trying to take your solution, which I follow... mostly, and adjust it to work with dynamic date ranges.

     

    I'm putting your formula in the Items property of the Column chart

     

    Here's what I have so far, but I'm unclear on how to get the count, and I'm not sure if my Sequence() will actually work. I think I'm close but not sure where to go next

     

    With({_Data:
    AddColumns(
    Filter(Assets,WarrantyExpireDate >= DateValue(WarrLSStart) && WarrantyExpireDate >= DateValue(WarrLSEnd)),"_YearMonth",Year(WarrantyExpireDate) & Text(Month(WarrantyExpireDate),"mm"))},
    ForAll(Sequence(DateDiff(WarrLSStart,WarrLSEnd,Months)),{_CountPerMonth:CountRows(Filter(_Data,not sure how to filter here))}))

     

    Thanks

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete