Customers turnover - From YTD to Monthly figures in Power Query

[Syndicate_Admin]

Hi everyone,

as you may see from the file I attached I have a dataset of YTD turnovers for each Customer Code and for each month.

My desired output is the one in Column F, that is obtaining Monthly turnover for each Customer Code/Month.

How can I achieve that in Power Query?

Thank you so much in advance for any help

Mark

[v-yanjiang-msft · Accepted solution]

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

[Syndicate_Admin]

I am trying to create an implicit measure in PowerPivot but there is not an OFFSET function available, so I cannot compile the code.

How can we solve that?

I am working on an Office 365 Subscription.

Thank you very much

[ThxAlot]

Monthly.pbix

[ThxAlot]

Monthly.pbix