Calculate YTD values with Power Query

[cr731]

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

Customer	Period	Volume	Revenue	Margin
A	8/1/2016	30	20	10
A	9/1/2016	30	20	10
B	7/1/2016	10	5	2
B	9/1/2016	8	4	2

<tbody>
</tbody>

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

Customer	MTD - Volume	MTD - Revenue	MTD - Margin	YTD - Volume	YTD - Revenue	YTD - Margin
A	30	20	10	60	40	20
B	8	4	2	18	9	4

<tbody>
</tbody>

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.