Custom Collection for Chart

[meinjoeskii]

I'm trying to create a Line Chart that shows Total Sales by Month. 

I have 2 Sharepoint List. 

1) Customer  2) Customer Log

Sales should be based on the Price column in my Customer list.

Customer Log contain CustomerID column to link it to Customer list and LogTime column which is a DateTime type (Include Time). Format is 1/10/2024 10:15PM. The Months in my Chart should based upon LogTime month. So if there's like January. February and April only sales, The chart should not display March.

I have a button that I want to OnSelect, create a collection then push it to the Line Chart. But it doesn't work. Please help! Thanks in advance. 

ClearCollect(
    MonthlySales,
    AddColumns(
        GroupBy('Customer Log', Text(LogTime, "yyyy-mm"), "MonthlySales"),
        "MonthYear",
        Text(LogTime, "yyyy-mm"),
        "TotalSales",
        Sum(MonthlySales, LookUp('Customer', CustomerID = 'Customer Log'.CustomerID, Price))
    )
)

PS: One of the error is it couldn't recognize LogTime.

[v-yueyun-msft]

Hi , @meinjoeskii 

This is my test sharepoint list and the end result in my Power Apps:

And we can use this code to get the collection in Power Apps:

ClearCollect(
    MonthlySales,
    ForAll(
    GroupBy( AddColumns( 'Customer Log' , "LogTimeText" ,  Text(LogTime, "yyyy-mm") ) ,"LogTimeText" , "MonthlySales" ),
    {LogTimeText : ThisRecord.LogTimeText , MonthlySales  : Sum( AddColumns( ThisRecord.MonthlySales As t1, "Price" , LookUp(Customer ,'Customer ID' = t1.CustomerID).Price )  ,Price) })


)

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

[meinjoeskii]

I am so sorry. I was thinking the whole time I left a very important part out. Let me explain. So the Customer list is suppose to be when I first created a Customer. Then follow by Customer Log list which you can log multiple sales activity for the same customer. There is a status column for both list. Every time a new entry (containing status) of the customer is added in Customer Log list, status on Customer list will be updated as well. Now I only want for when the Customer Log status column is equals to "Closed" then find the LogTime.

So for example there are 10 activity in Customer Log, 6 of them status are closed.

2 in Jan, 2 in February, 2 in April.

Now we have the months.

Next step, find the Price of 6 ID's in Customer list then sum them up according to months. Hence we got the Sales.

So the Line Chart should show X = Jan, Feb, April. Y= Sum of Jan, Sum of Feb, Sum of April. 

Thank you so much for answering my question. You have no idea how much you're helping me. I really appreciate you.

[v-yueyun-msft · Accepted solution]

Hi , @meinjoeskii 

Thanks for quick response! Do you mean you have a [Status] column in your Customer Log List , and you want to filter the [Status] = "Closed" ?

For example , i create a Status column in my test SharePoint List:

And i use this code in Power Apps:

ClearCollect(

    MonthlySales,

    ForAll(

    GroupBy( AddColumns( Filter( 'Customer Log' , Status.Value = "Closed")   , "LogTimeText" ,  Text(LogTime, "yyyy-mm") ) ,"LogTimeText" , "MonthlySales" ),

    {LogTimeText : ThisRecord.LogTimeText , MonthlySales  : Sum( AddColumns( ThisRecord.MonthlySales As t1, "Price" , LookUp(Customer ,'Customer ID' = t1.CustomerID).Price )  ,Price) })

)

If I misunderstand your needs, can you give me some test data and the end result you want to get? The data is clearer than the words!

Best Regards,

Yueyun Zhang

[meinjoeskii]

Thank you so much for your help. It works exactly like I wanted it! Appreciate your help!