# Calculate YTD values with Power Query



## cr731 (Oct 31, 2016)

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


CustomerPeriodVolumeRevenueMarginA8/1/2016302010A9/1/2016302010B7/1/20161052B9/1/2016842

<tbody>

</tbody>
I would like to use Power Query to create a table that outputs the following:


CustomerMTD - VolumeMTD - RevenueMTD - MarginYTD - VolumeYTD - RevenueYTD - MarginA302010604020B8421894

<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 (Oct 31, 2016)

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 (Apr 27, 2020)

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 (Apr 27, 2020)

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


----------

