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

[sienna28]

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

[sienna28]

Thanks - seems a bit better, but all the labels and series seem to only have count available to select and not month.

[RandyHayes]

@sienna28 

I see I had a misplaced paren in my formula (sorry, we type these things by hand in the forum without the aid of the formula editor!).

Should be:

With({_items:
    AddColumns(
        Filter(AutoWork, OrderCompleteDate >= ddYear.Selected.Start && 
                         OrderCompleteDate<= ddYear.Selected.End
        ),
        "_month", Month(OrderCompleteDate)
    )
    },

    ForAll(Sequence(12),
        {_month: Last(FirstN(Calendar.MonthsShort(), Value)).Value,
         _count: CountRows(Filter(_items, _month=Value))
        }
    )
)

[sienna28]

Wow - that looks really great....thank you very much for helping me.

I will need to ask you a little about some of the logic when I digest it better.

But first...I wondered what the circular green count label at the bottom was for and if it denotes anything?

[RandyHayes]

@sienna28 

Very good!

That which you are seeing is the Legend.  If you had multiple series, I would say it would be helpful, but if you only have one, you might just remove it.

[sienna28]

Thanks Randy.

Just wondered if you could help me understand the code a little as some of the statements I've not come across before...

Bit unsure about this statement - With({_items:
From what I can gather - First it does the filter pulling out the items in the date range.
Then assigns these dates to a column called _month.

ForAll(Sequence(12) - Sets up a table with 12 rows
Creates a column called _month and _count
Bit unsure about this - Last(FirstN(Calendar.MonthsShort(), Value).Value)

And generally what the for all is doing...

Appreciate any help understanding....

[RandyHayes · Accepted solution]

@sienna28 

Sure thing.  

Bit unsure about this statement - With({_items:

The With function (read more here) creates a scoped variable.  In this case we are creating a variable called _items then we are assigning a table to it based on the AddColumns/Filter statement.  With variables can be used anywhere in the context of the With statement, but not outside.  So, this gives you a variable you need to do this, without creating a variable to clutter your app.

From what I can gather - First it does the filter pulling out the items in the date range.

Yes, the Filter is the innermost table formula.  It Filters the AutoWork datasource returning only records that are within the Start and End dates as selected from the Dropdown selection record.

Then assigns these dates to a column called _month.

Yes, the AddColumns will add a column to the results of the Inner filter table called "_month" that will simply be set to the value of the OrderCompleteDate month as determined by the Month function.

ForAll(Sequence(12) - Sets up a table with 12 rows

Yes, Sequence will always return a single column table with X number of rows (as determined by the parameter supplied).  In this case, the Sequence will return a table with 12 rows that has a Value column with the sequential number of the sequence.

Creates a column called _month and _count

The ForAll function is a function that returns a table (one of the more powerful table functions in PowerApps).

It takes a table as its first parameter (in this case the table result from Sequence) and then ForAll iterations of the records in the table, it will add a row with whatever you want - in this case, a row with a _month and a _count column.

Bit unsure about this - Last(FirstN(Calendar.MonthsShort(), Value).Value)

PowerApps has a Calendar object in it that has a MonthsShort action on it that returns a table of records that have all the Short names of the month in it.  The names are in a Value column in that table that is returned from the action.  

The way to get a sequential record out of that table is to take the LAST record of the FIRST x number of records.  Since our Sequence (which remember is a table of numbers 1-12 that are in a Value column in that table) provides the record number we want, we just use that in the formula.  SO, for example, as the ForAll iterates through the Sequence table, let's say it gets to record 6 (Value of 6), in the formula (working inner to outer), the FirstN function will return a table of 6 records of the MonthsShort table.  This is a table though and we need the record.  So, if we take the Last record of that table, we will now have the single 6th record from that table.  Finally, since that months table has a column called Value, we just reference that at the end.  

NOTE: your question above is not syntactically correct.  The correct formula is : Last(FirstN(Calendar.MonthsShort(), Value)).Value  In this case the inner FirstN returns that table, the Last returns the last record, and the .Value returns the text value.

And generally what the for all is doing...

Hopefully all that I mentioned above has cleared up what it is doing.  Remember, ForAll is a Table producing function.  You will see WAY too many bad examples online of using the ForAll as a For/Loop function ... it is not!  And it should not be looked at that way.  In fact, sadly enough, even the documentation on the ForAll sites a bad example of how to use it.  It was designed to be the ultimate data shaping function that has the most power and functionality to return tables with ANY schema you want.

Hopefully this is clear and helpful for you.

[sienna28]

Thanks Randy - that make things much clearer.

One more thing - I know in PowerApps it is possible to look at the value of variables and collections under the File menu. Is it possible to see the values this way of the 

With({_items:
ForAll(Sequence(12),

[RandyHayes · Accepted solution]

@sienna28 

Glad it is clearer.

No, the scoped With variables are only valid in the context of the With statement, so it will not appear as a variable (because it is not an application variable).  However, the formula editor will show you specific parts of the formula and their values as long as they are not dependent on the actual With variable.

[iwonder]

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

[iwonder]

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
                )
            )
        }
    )
)