Automatic Custom Columns with YTD Calculation

[MarkM1]

Good Morning,

I am enclosing a screenshot of a Power Query instance I am working on.

Query

The Table shows figures of following fields combinations:

• CATEGORY (Frames, Lenses)
• SUPPLIER (Supplier 1, Supplier 2)
• CLASS (A, B)
• AGGREGATE (Sell-Out, Sell-In)
• MONTHS (from January to June)

The Table derives from a Query and the database is supposed to be updated each month.

What I am trying to achieve, but I am unable to, is to create a custom column with the YTD calculation for each single month.

In this example, being the months 6 (From Jan to Jun) there should be 6 additional columns as follows:

• YTD January (equals to January naturally)
• YTD February
• YTD March
• ... and so on

calculating YTD for each combination Category/Supplier/Class/Aggregate, and naturally once the query is refreshed after updating the database, columns shall upgrade too considering the number of months inside the database.

Can you please help me?

Thank you

Mark

[jgeddes]

Here is an example of one possible approach to this. 
I have created a small sample set that mimics your data. Please copy this code into the advanced editor of a blank query so you can review each step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9dDoAgCADgu/DsJhAid3E+1P0PkdlkrrIHmYxv/JQCOwQg3lrsv/aQInJkZLlLJlDDAlJymFS0w6NlkvQBtwH5qi0d5+F0uI/BHGlyf8ycZcs+9u14aie2huh3WN+vng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, SUPPLIER = _t, CLASS = _t, AGGREGATE = _t, MONTH = _t, VALUE = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"CATEGORY", type text}, 
            {"SUPPLIER", Int64.Type}, 
            {"CLASS", type text}, 
            {"AGGREGATE", Int64.Type}, 
            {"MONTH", type date}, 
            {"VALUE", Int64.Type}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"CATEGORY", "SUPPLIER", "CLASS", "AGGREGATE"}, 
        {
            {"January_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 1)[VALUE]), type nullable number},
            {"February_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 2)[VALUE]), type nullable number},
            {"allRows", each _, type table [CATEGORY=nullable text, SUPPPLIER=nullable number, CLASS=nullable text, AGGREGATE=nullable number, MONTH=nullable date, VALUE=nullable number]}
        }
    ),
    #"Expanded allRows" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "allRows", 
        {"MONTH", "VALUE"}, 
        {"MONTH", "VALUE"}
    )
in
    #"Expanded allRows"

[MarkM1]

Hi Jgeddes,

first of all thanks for your help. I run the code but it seems it does not work properly. Here follows a snapshot from Power Query Editor:

Is there any way to upload a file here in the forum? This way I could provide you with a structured example.

Mark

[jgeddes · Accepted solution]

There is no way for you to attach a file directly. Generally a shared cloud storage location that is accessable to a public user would be the only way to share a file. 
Specific to the code, it may be an issue with date formating. 

Try this version to see if it removes the errors...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RDoAgCADQu/DtJiIid3F+1P0PEdkqavoBg/EGtAYbBEiULY/KAiliioTE10gZepjBVDwswjLgbh0X8TA7SOds7qg6J7ebHDZHr1sy9axqfc7+HH3XsS4gZgd1/NcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, SUPPLIER = _t, CLASS = _t, AGGREGATE = _t, MONTH = _t, VALUE = _t]),
    #"Parsed Date" = 
    Table.TransformColumns(
        Source,
        {
            {"MONTH", each Date.From(DateTimeZone.From(_, "de-DE")), type date}
        }
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        #"Parsed Date",
        {
            {"CATEGORY", type text}, 
            {"SUPPLIER", Int64.Type}, 
            {"CLASS", type text}, 
            {"AGGREGATE", Int64.Type}, 
            {"MONTH", type date}, 
            {"VALUE", Int64.Type}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"CATEGORY", "SUPPLIER", "CLASS", "AGGREGATE"}, 
        {
            {"January_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 1)[VALUE]), type nullable number},
            {"February_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 2)[VALUE]), type nullable number},
            {"allRows", each _, type table [CATEGORY=nullable text, SUPPPLIER=nullable number, CLASS=nullable text, AGGREGATE=nullable number, MONTH=nullable date, VALUE=nullable number]}
        }
    ),
    #"Expanded allRows" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "allRows", 
        {"MONTH", "VALUE"}, 
        {"MONTH", "VALUE"}
    )
in
    #"Expanded allRows"