
Active Member

July 16, 2020

Hello,
I have forecast and demand data for certain products for each month for 12 months. There hundreds of products, so for each month there are hundreds of rows with the forecast/demand data. I am calculating the difference between the actual demand data and the forecast data and wish to display a column that shows the total summed difference for each month. So, for all the products demanded in the month of 5/1/2015, the column will display the total difference between the forecast data and the actual demand for each product during that month. Any help would be appreciated. I will also gladly provide any additional needed information.
Thanks in advance!

Active Member

July 16, 2020

Hello Mynda,
I appreciate the response. However, I am interested in a solution that can be done within Power Query. I will provide a sample text example below.
period part demand forecast variance TOTAL VARIANCE BY MONTH
5/1/2015 A 10 9 1 4
5/1/2015 B 5 7 2 4
5/1/2015 C 5 6 1 4
6/1/2015 A 3 3 0 7
6/1/2015 B 8 2 6 7
6/1/2015 C 7 6 1 7
7/1/2015 A 7 2 5 14
7/1/2015 B 6 9 3 14
7/1/2015 C 4 10 6 14
I hope the formatting is clear. The column on the far right is the column I wish to generate within Power Query.


November 8, 2013

It's not recommended to do that in PQ, it's better to make the aggregations in a pivot table, see image attached. The table you have is perfect for that.
If you still need that, use the query below:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"period ", type datetime}, {"part ", type text}, {" demand ", Int64.Type}, {"forecast ", Int64.Type}, {" variance ", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"period "]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([#"period "]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Month",{{" variance ", "variance"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", (x)=> List.Sum(Table.SelectRows(#"Renamed Columns", each _[Year]=x[Year] and _[Month]=x[Month])[variance]))
in
#"Added Custom"

Answers Post
1 Guest(s)
