Calculate YTD values with Power Query

[cr731]

I have a data table containing rows of monthly data, similar to below,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Period[/TD]
[TD]Volume[/TD]
[TD]Revenue[/TD]
[TD]Margin[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/1/2016[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/1/2016[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7/1/2016[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/1/2016[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I would like to use Power Query to create a table that outputs the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]MTD - Volume[/TD]
[TD]MTD - Revenue[/TD]
[TD]MTD - Margin[/TD]
[TD]YTD - Volume[/TD]
[TD]YTD - Revenue[/TD]
[TD]YTD - Margin[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

This assumes that the "current" month is September 2016 (not necessarily always the latest month of the data, so the month designated as current needs to be a parameter type selection).

At first I thought this would be fairly simple but when I actually started working on it, I am drawing a blank.

 

[MarcelBeug]

Date 9/30/2016 in TableCurrent with column label Current.
Data in Table1.

let
    Current = Date.From(Excel.CurrentWorkbook(){[Name="TableCurrent"]}[Content][Current]{0}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Period", type date}, {"Volume", Int64.Type}, {"Revenue", Int64.Type}, {"Margin", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Period] <= Current and [Period] >= #date(Date.Year(Current), 1, 1)),
    #"Added MTD Volume" = Table.AddColumn(#"Filtered Rows", "MTD Volume", each if Date.Month([Period]) = Date.Month(Current) then [Volume] else 0),
    #"Added MTD Revenue" = Table.AddColumn(#"Added MTD Volume", "MTD Revenue", each if Date.Month([Period]) = Date.Month(Current) then [Revenue] else 0),
    #"Added MTD Margin" = Table.AddColumn(#"Added MTD Revenue", "MTD Margin", each if Date.Month([Period]) = Date.Month(Current) then [Margin] else 0),
    #"Grouped Rows" = Table.Group(#"Added MTD Margin", {"Customer"}, 
                        {{"YTD Volume", each List.Sum([Volume]), type number}, 
                         {"YTD Revenue", each List.Sum([Revenue]), type number}, 
                         {"YTD Margin", each List.Sum([Margin]), type number}, 
                         {"MTD Volume", each List.Sum([MTD Volume]), type number}, 
                         {"MTD Revenue", each List.Sum([MTD Revenue]), type number}, 
                         {"MTD Margin", each List.Sum([MTD Margin]), type number}}),
    #"Reorder Columns" = Table.ReorderColumns(#"Grouped Rows",{"Customer","MTD Volume","MTD Revenue", "MTD Margin","YTD Volume","YTD Revenue", "YTD Margin"})
in
    #"Reorder Columns"

 

[Drewbbc]

Marcel - trying to replicate the code posted here - Calculate YTD values with Power Query & pasted below. reachable @ drew@bambic.com


let
Current = Date.From(Excel.CurrentWorkbook(){[Name="TableCurrent"]}[Content][Current]{0}),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Period", type date}, {"Volume", Int64.Type}, {"Revenue", Int64.Type}, {"Margin", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Period] <= Current and [Period] >= #date(Date.Year(Current), 1, 1)),
#"Added MTD Volume" = Table.AddColumn(#"Filtered Rows", "MTD Volume", each if Date.Month([Period]) = Date.Month(Current) then [Volume] else 0),
#"Added MTD Revenue" = Table.AddColumn(#"Added MTD Volume", "MTD Revenue", each if Date.Month([Period]) = Date.Month(Current) then [Revenue] else 0),
#"Added MTD Margin" = Table.AddColumn(#"Added MTD Revenue", "MTD Margin", each if Date.Month([Period]) = Date.Month(Current) then [Margin] else 0),
#"Grouped Rows" = Table.Group(#"Added MTD Margin", {"Customer"},
{{"YTD Volume", each List.Sum([Volume]), type number},
{"YTD Revenue", each List.Sum([Revenue]), type number},
{"YTD Margin", each List.Sum([Margin]), type number},
{"MTD Volume", each List.Sum([MTD Volume]), type number},
{"MTD Revenue", each List.Sum([MTD Revenue]), type number},
{"MTD Margin", each List.Sum([MTD Margin]), type number}}),
#"Reorder Columns" = Table.ReorderColumns(#"Grouped Rows",{"Customer","MTD Volume","MTD Revenue", "MTD Margin","YTD Volume","YTD Revenue", "YTD Margin"})
in
#"Reorder Columns"

 

[Drewbbc]

PS -- getting an error in the 'Current' line.