Filter based on month only & sum a column based on the filtered month

(0) Share

Report

Posted on by LugenG

107

Hi guys. Please can you help. Can anyone spot the issue with my formula that is on the Items property of my Gallery?

Sum(Filter(CWAS_AdvisorActivityTracker; Month(WeekStart) >= Month(DatePicker2_2.SelectedDate) && Month(WeekStart) <= Month(DatePicker3_2.SelectedDate)

My SP list (CWAS_AdvisorActivityTracker) has a WeekStart and a WeekEnd column. Both are Date and Time column types.

I want to filter based on month only from the WeekStart column in my SP list and then have the records in the rows adjacent to the filtered month summed up.

So for e.g.;

WeekStart - No. Of Referrals

01/02/2024 - 12

01/03/2024 - 13

05/06/2024 - 1

18/03/2024 - 7

If the user selects March 2024 from the Date Picker, I need 20 displayed (sum of 13 & 7)

Or should have a 2 dropdowns, one for Month & one for Year to make it easier? But then what would be the correct formula to Sum & Filter from my Gallery?

Please help - thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (17)

Answers (1)

v-yueyun-msft on at

Like (0)

Report
Hi , @LugenG

You can try to use this code:

Sum( Filter(CWAS_AdvisorActivityTracker,Month( WeekStart) = Month(DatePicker1.SelectedDate) && Year(WeekStart)= Year(DatePicker1.SelectedDate) ) ,'No. Of Referrals')

This is my test data :

ClearCollect(testdata, { WeekStart: DateTimeValue("2/1/2024 00:00 AM") , 'No. Of Referrals': 12}, { WeekStart: DateTimeValue("3/1/2024 00:00 AM") , 'No. Of Referrals': 13}, { WeekStart: DateTimeValue("6/5/2024 00:00 AM") , 'No. Of Referrals': 1}, { WeekStart: DateTimeValue("3/18/2024 00:00 AM") , 'No. Of Referrals': 7} );

Label- Text:

Sum( Filter(testdata,Month( WeekStart) = Month(DatePicker1.SelectedDate) && Year(WeekStart)= Year(DatePicker1.SelectedDate) ) ,'No. Of Referrals')

The result is as follows:

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you. If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
LugenG 107 on at

Like (0)

Report

Thanks very much, your code made senses to me and I can see it will work, but doesn't work in my application unfortunately, possibly due to me explaining my issue incorrectly so let me try & put it differently.

SP List: CWAS_AdvisorActivityTracker
Columns:
Advisor (person column)
WeekStart (date and time column, but as a Short Date, no time included)
WeekEnd, (date and time column, but as a Short Date, no time included)
No. of Referrals
No. of Telephone calls
No. of Appointments
Fees Earned, etc, etc.
There's about 15 or 16 columns

Now currently in my App, I have a Gallery with all the columns from my SP list.
Then I have 2 date pickers which when I select a WeekStart date, it filters and displays the values of all the columns that are entered in that specific date in the Gallery. This is the code:
Filter(CWAS_AdvisorActivityTracker; WeekStart >= DatePicker2_2.SelectedDate && WeekEnd <= DatePicker3_2.SelectedDate)

I now want to create 2 Dropdowns for Month & Year and use the same principle as above which is when I select a month & year, the app filters all values for the specific month and year, sums it up, and displays it for me, per all the columns.

Perhaps this will be easier to do, I'm hoping 😞

OR, I would need to edit my existing code above to somehow do this and not use dropdowns?

I'm thinking the 2 dropdowns would be easier so that I can then choose if I want to see values per week or summed up values per month.

Really hoping someone can assist with this. It's taking so much time, I have a huge headache 😞

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

Hi , @LugenG

Do you mean you want to get the sum of ['No. Of Referrals'] in the current WeekStart Datepicker's Year Month based on your current filter:
Filter(CWAS_AdvisorActivityTracker; WeekStart >= DatePicker2_2.SelectedDate && WeekEnd <= DatePicker3_2.SelectedDate)

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
LugenG 107 on at

Like (0)

Report

Yes please, I want the sum of ['No. Of Referrals'] if I select a certain Month and Year in my DatePicker.

Correct.

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report
Hi , @LugenG

Can you try to use this code:

Sum(Filter(CWAS_AdvisorActivityTracker; WeekStart >= DatePicker2_2.SelectedDate && WeekEnd <= DatePicker3_2.SelectedDate && Month(WeekStart)= Month(DatePicker2_2.SelectedDate) &&Year(WeekStart)= Year(DatePicker2_2.SelectedDate) ),'No. Of Referrals')

Or you can try this code:

Sum( Filter(CWAS_AdvisorActivityTracker,WeekStart>= DatePicker2_2.SelectedDate && WeekEnd <= DatePicker3_2.SelectedDate && WeekStart>= Date(Year(DatePicker2_2.SelectedDate),Month(DatePicker2_2.SelectedDate),1) && WeekStart<= Date(Year(DatePicker2_2.SelectedDate),Month(DatePicker2_2.SelectedDate)+1,0) ) ,'No. Of Referrals')

And how many rows you have in your Sharepoint list? The sum() function is not delegable function for SharePoint.

For more information, you can refer to :
Solved: Re: How to Overcome Delegation with SUM function - Power Platform Community (microsoft.com)

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
LugenG 107 on at

Like (0)

Report

Thanks so much, but both aren't working 😞
I only have test data in because I didn't publish my app yet, so only 2 rows of data and about 10 blank rows.

Do I need to specify the column ['No. Of Referrals'] at the end of the code? I know that's one of the sums I'm looking for, however can it not pull up all values and display it in the gallery based on the month & year I selected in my DatePicker?
I'm just thinking my current code does not specify a column, but it can obviously only display a single value for all my columns for that specific line entry in my SP list, based on which date I choose. It then display the values in my gallery.

I want the sum of all columns for all rows that match the month & year that I select, displayed in my gallery.

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report

Hi, @LugenG

This my sharepoint list:

And this is the result in my Power Apps;

As the WeekStart datepicker is "2024/2/27" so it filter the only 2024-2 in the [StartWeek] column.. Dose i misunderstand your need?

Can you show me your current code and the display in your app?

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
LugenG 107 on at

Like (0)

Report

No, you correct. You are understanding my need.

However, lets say you had another column on your sharepoint list called Number with the following

Title WeekStart WeekEnd Number
1104-0 3/1/2024 3/14/2024 5
1104-1 2/27/2024 3/12/2024 10
1104-2 2/26/2024 3/19/2024 3
1105-1 3/13/2024 3/28/2024 1
1106-2 3/4/2024 3/9/2024 6
1107-0 3/4/2024 3/26/2024 7

So based upon your WeekStart datepicker of 2024/2/27 above, it must now sum up all the values in the Number column that match for 2024-2 which are 10 and 3, so it must display as 13 in my Gallery

Even if you had to choose your WeekStart datepicker as 2024/2/1, it must still filter only for all 2024-02 values in the Number column, sum it, and display in the gallery

Let me know I must explain a little better

Was this reply helpful? Yes No
Verified answer

v-yueyun-msft on at

Like (0)

Report
Hi , @LugenG

I can realize it in my app, like this:

Sum( Filter(boxes, WeekStart>= Date(Year(DatePicker2_2.SelectedDate),Month(DatePicker2_2.SelectedDate),1) && WeekStart<= Date(Year(DatePicker2_2.SelectedDate),Month(DatePicker2_2.SelectedDate)+1,0) ) ,'No. Of Referrals')

And can you show what's the code you use in your side and the screenshot in your App?

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
LugenG 107 on at

Like (0)

Report

Aah man, this is what I am getting 😞

I see yours is working, yes.

I don't know anymore 😞 Why is my code not working?

Was this reply helpful? Yes No