Sum of amount per month

[Anonymous]

hello all,

i have a sharepoint list with following columns:

Week no, Amount per week , Month, Sum of amount per month

I want to Sum the amount of only the following month and for the next month it will calculate the sum of amount according to its own month.

For example:

September will have its own sum of amount per month and then october will have its own sum of amount. I hope i made it clear enough to make you understand my problem. Any help will be appreciated.

[RandyHayes · Accepted solution]

@Anonymous 

You have not provided any context to your design (i.e. gallery, stand-alone labels, etc) to provide clear response.

But, in general, you can use the GroupBy function to provide what you need.

Ex:

AddColumns(
    GroupBy(yourList,
        "Month", "_data"
    ),
    "_monthSum", Sum(_data, 'Sum of amount per month')
)

I hope this is helpful for you.

[DEV_CM]

How are you defining the Week no? Is it the Week no out of the  whole year (e.g. week 2 out of 52) or do you have it broken up by months (e.g. week 1 - September)?

[Anonymous]

Thanks for your reply @RandyHayes ,

and I'm sorry  I did not give a clear context. so Week no, month and sum of amount per month are labels and calculated within power apps.

Amount per week will be given by user through input field. Based on this user input, sum of amount per month should be calculated. 

[Anonymous]

Hello @DEV_CM ,

Im using WeekNum(Today()) to extract the current week no.

[DEV_CM]

Got it. Looks like @RandyHayes posted a viable solution!

[RandyHayes]

@Anonymous 

Ah, you stated in your post that:

"I have a sharepoint list with following columns:

Week no, Amount per week , Month, Sum of amount per month"

So what data do you have??  

I am not getting a clear picture on what you are starting with and what you are trying to achieve. I mean, I know what you want to achieve as far as the calculations, but are you trying to display this or store this or what?

[Anonymous]

@RandyHayes,

I'm sorry for any confusion. I'm new to this community and power platform. 

Basically, at this time SP list is empty(it has only column names right now), my goal is to populate the list through power apps and display the contents of List in form of gallery in power apps.

the user will be able to view the content and if he clicks on add button a new list will be created for user to enter amount per week. as i said other fields like month, week no and sum of amount per month will be calculated in power apps itself.

when user clicks on Save button the data will be patched to SP list.

This is my SP list  (SOAPM=> sum of amount per month)

This is the screen where user will enter amount,

the other field will not be visible at that time.

[RandyHayes · Accepted solution]

@Anonymous 

So then it is sounding like a Gallery is your starting point!

In that Gallery you would have the ability to enter a date.  I would recommend (for purposes of delegation later) to store the month from the date entry.

You state a "Form" in your response - so I am assuming the user will enter data for the list in a EditForm in your app.  

If so, then you will have a Month column in your form.  It need not be visible to the user as you will be calculating the month, but it needs to be in the form for when you do a SubmitForm to submit your data.

Assuming you have a date picker control in your form at some point, you can derive all you need from that.

So, in the Month datacard in your form, you would alter the Update property to reflect from your date entry. The Formula: Month(yourDatePicker.SelectedDate) would complete that for you.

Likewise, the WeekNo column would have an update property of WeekNum(yourDatePicker.SelectedDate)

NOTE: if any of the above are to "be seen" and not hidden, then alter the Default property of the DataCardValue control in those fields in the form...not the Update.  Only alter the Update if you expect to not show the datacard.

I would omit the "Sum of amount per month" in your data list as it is not relevant at the record level but is only derived from monthly data.

NOW...back to the original formula I provided...that would now apply for getting the total sum by months.

[Anonymous]

@RandyHayes  thanks for helping me out. I also watched your youtube video of time tracker it was really helpful to me for another project.