Create Column that subtracts previous value from next value based on other column criteria

[AuroraNI]

HI all,

Can someone help with Dax code to create a new column in query editor.  I would like to subtract the previous day's deaths from the next days for each specific country, I am struggling with figuring out to do this when it changes country.

[FrankAT · Accepted solution]

Hi @AuroraNI ,

1. Group column Country/Region.
2. Take a look at the mashup code:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFNCsMgEAXgqwTXgZhR87MM7QlaupIsLJU0UBRsuujtK0jDZAxZiX4wT+ZpzU7+45bwrS52mr0rzmaxrGT/w5rl+S7YWGo2vO7GzSa+QldxUQEHHi81p9ofqeBYa6o1VkWUR5WZTjak3N3Jq+5OXhWwtlTFoUqsPVWFFLJfNVizX7VYG6q4BchycQsi5d6uA21AKCx4R1Ii2WxedSnLPXwIeWtAdNtanDr+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Country/Region Date", "Country/Region"}, {"Deaths ", "Deaths"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Deaths", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country/Region"}, {{"Country", each _, type table [#"Country/Region"=text, Date=date, #"Deaths"=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Running Total", each fxFunction([Country])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Running Total"}),
    #"Expanded Running Total" = Table.ExpandTableColumn(#"Removed Other Columns", "Running Total", {"Country/Region", "Date", "Deaths", "Saldo"}, {"Country/Region", "Date", "Deaths", "Saldo"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Running Total",{{"Country/Region", type text}, {"Date", type date}, {"Deaths", Int64.Type}, {"Saldo", Int64.Type}})
in
    #"Changed Type1"

// fxFunction
(tabelle as table) =>
let
    #"Sorted Rows" = Table.Sort(tabelle,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Saldo", each List.Sum(List.Range(#"Added Index"[Deaths],0,[Index]))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Saldo", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Index"})
in
    #"Removed Columns"

Regards FrankAT

[FrankAT · Accepted solution]

Hi @AuroraNI ,

1. Group column Country/Region.
2. Take a look at the mashup code:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFNCsMgEAXgqwTXgZhR87MM7QlaupIsLJU0UBRsuujtK0jDZAxZiX4wT+ZpzU7+45bwrS52mr0rzmaxrGT/w5rl+S7YWGo2vO7GzSa+QldxUQEHHi81p9ofqeBYa6o1VkWUR5WZTjak3N3Jq+5OXhWwtlTFoUqsPVWFFLJfNVizX7VYG6q4BchycQsi5d6uA21AKCx4R1Ii2WxedSnLPXwIeWtAdNtanDr+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Country/Region Date", "Country/Region"}, {"Deaths ", "Deaths"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Deaths", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country/Region"}, {{"Country", each _, type table [#"Country/Region"=text, Date=date, #"Deaths"=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Running Total", each fxFunction([Country])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Running Total"}),
    #"Expanded Running Total" = Table.ExpandTableColumn(#"Removed Other Columns", "Running Total", {"Country/Region", "Date", "Deaths", "Saldo"}, {"Country/Region", "Date", "Deaths", "Saldo"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Running Total",{{"Country/Region", type text}, {"Date", type date}, {"Deaths", Int64.Type}, {"Saldo", Int64.Type}})
in
    #"Changed Type1"

// fxFunction
(tabelle as table) =>
let
    #"Sorted Rows" = Table.Sort(tabelle,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Saldo", each List.Sum(List.Range(#"Added Index"[Deaths],0,[Index]))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Saldo", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Index"})
in
    #"Removed Columns"

Regards FrankAT

[AuroraNI]

HI @FrankAT thanks for the help.

How would you use this code?  In query editor?

[FrankAT · Accepted solution]

Hi @AuroraNI ,

yes, use it in Power Query!

Regards FrankAT

[amitchandak]

@AuroraNI ,

Try like

diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])),[Death])

[AuroraNI]

@amitchandak i get the follow error:

Thanks for your help with this

[amitchandak]

@AuroraNI ,I think I forgot -1

diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])-1),[Death])

[AuroraNI]

@amitchandak still getting the same error?  Is it anything to do with how it deals with the first row?

[amitchandak]

-1 is outside )
diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])-1),[Death])

[AuroraNI]

Hi @amitchandak still not working