Customers turnover - From YTD to Monthly figures in Power Query

Hi Syndicate_Admin ,

According to your description, here's my solution.

Add a custom column.

if[DATE]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME])[DATE])then [YTD TURNOVER] else[YTD TURNOVER]-Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME] and x[DATE]=List.Max(Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME]and x[DATE]<[DATE])[DATE]))[YTD TURNOVER]{0}

Get the expected result:

Here's the whole code, you can copy-paste in a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldKxCoMwEIDhV5HMB95dEpPMhU4tlK7iINWh0CrE9v2bSAt1yIGDkEM+Ev6kbRXVmmpG1goUIlL6jDNpOLyX1/wcY0VpOM3TME9pURFoxEp1UKDun3IaLn28L1maRnBh4/Kfa1pl5kKZWdywfOxzP8T7kGEDVjippQ21aTg+5jhOt3VXo7+Ua/b7+gSUaLmPD4Ir9yEwwoZiIQ+hEahcyP2o3vmCGLyTaLlQentekOVGDGTKUGxECEyClSOF9U67Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, #"CUSTOMER CODE" = _t, #"CUSTOMER NAME" = _t, #"CUSTOMER CITY" = _t, #"YTD TURNOVER" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"CUSTOMER CODE", Int64.Type}, {"CUSTOMER NAME", type text}, {"CUSTOMER CITY", type text}, {"YTD TURNOVER", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[DATE]=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME])[DATE])then [YTD TURNOVER] else[YTD TURNOVER]-Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME] and x[DATE]=List.Max(Table.SelectRows(#"Changed Type",(x)=>x[CUSTOMER NAME]=[CUSTOMER NAME]and x[DATE]<[DATE])[DATE]))[YTD TURNOVER]{0})
in
    #"Added Custom"

I attach my sample below for your reference.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang