Cumulative Query Summary - Is it possible?

[Anonymous]

Good Morning Power BI Community,

I have a business need that I am looking to fill and was wondering if this is possible in Power BI & Power Query.

Basically I have a query that is run  on a daily basis via an odbc connection to Power Query that contains open invoice detail for that day listed by invoice number and customer similar to the table below:

Customer	Invoice Number	Invoice Amount	Current Amount	1-14 Days late	15-29 Days late	30 - 59 Days late	60 or more days late
Customer A	123	500	500
Customer A	124	500		500
Customer A	125	500			500
Customer A	126	500				500
Customer A	127	500					500
Customer B	128	500	500
Customer B	129	500	500
Customer B	130	500		500
Customer B	131	500				500
Customer B	132	500				500

The business need is to summarize each days query by customer number, date stamp each summary, and store that summary in a new table so that the historical data for day is preserved for comparision and tracking purposes, something like the table below.

Reporting Date	CustomerName	Sum of Invoice Amount	Sum of Current Amount	Sum of 1-14 Days late	Sum of 15-29 Days late	Sum of 30 - 59 Days late	Sum of 60 or more days late
9/28/2020	Customer A	2500	500	500	500	500	500
9/28/2020	Customer B	2500	1000	500		1000
9/29/2020	Customer A	2500	500	500	500	500	500
9/29/2020	Customer B	2500	1000	500		1000

I am trying to guage if this is possible and how technically difficult this would be. I have searched the web and was unable to find solutions exactly like this. I did however find the List.Accumulate function for M lists, would that be the way to go?

Any guidance would be appreciated and if possible point me in the direction of a solution.

Thanks,

Aaron

[Bohumil_Uhrin]

Hi @Anonymous, you can use group function for this task.

Assuming, you have date as a first column, you can remove "invoice number" column, and then group by date and customer, using sum as aggregation function, for each value column.

let
    Source = Excel.Workbook(File.Contents("path to excel file"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Invoice Number"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Customer"}, {{"Invoice Amount", each List.Sum([Invoice Amount]), type number}, {"Current Amount", each List.Sum([Current Amount]), type nullable number}, {"1-14 Days late", each List.Sum([#"1-14 Days late"]), type nullable number}, {"15-29 Days late", each List.Sum([#"15-29 Days late"]), type nullable number}, {"30-59 Days late", each List.Sum([#"30 - 59 Days late"]), type nullable number}, {"60 or more days late", each List.Sum([60 or more days late]), type nullable number}})
in
    #"Grouped Rows"

[Anonymous]

Maybe I should explain that the transactions recived from the query are going to change every day and I want to save a daily summary of those transactions past the point where they would be retrieved from the database.

Essentially a table that is accumulating the history over successive daily refreshes.

[Bohumil_Uhrin]

@Anonymous, ok, altough I dont understand exact steps how you do it, the group function should do the job (if you dont have date column in the first table, you should group only by customers)

[Anonymous]

@Bohumil_Uhrin  Yes, I agree Group By would be the way to go with the summary portion. Thank you.

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[Anonymous]

@Icey  Not solved yet. The real question is how to store cumulative summary of past queries in the data model somehow. I will try some ideas to do this.

[AlB]

Hi @Anonymous 

This is relatively simple in PQ but...  where is the date on the first table?? Can you post the full table including the data that needs to be processed?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

The date in the second table is generated by power query and would represent the date the query was refreshed and the summary was posted to the cumulative table. I can get an invoice date in the first table though. Does that help?

Thanks,

Aaron