Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @AuroraNI ,
// 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
Hi @AuroraNI ,
// 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
Try like
diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])),[Death])
@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])
@amitchandak still getting the same error? Is it anything to do with how it deals with the first row?
-1 is outside )
diff = [Death] - maxx(filter(Table,Table[Courty/Region] = earlier(Table[Courty/Region]) && Table[Date] = earlier(Table[date])-1),[Death])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |