Forum Discussion
Changes by Date and Items
hi all, i have a table in power bi desktop as in the following, i need to calculate the daily changes for each item starting from the minimum value whcih is already on the first date,new dates are added every day with new values (accumulated)
| Name | Date | Cumulative Value |
| item1 | date1 | 1 |
| item2 | date1 | 6 |
| item3 | date1 | 9 |
| item1 | date2 | 6 |
| item2 | date2 | 12 |
| item3 | date2 | 16 |
| item1 | date3 | 11 |
| item2 | date3 | 26 |
| item3 | date3 | 28 |
Hi Anonymous ,
You can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzTVU0lEyMjAy1DfRBzENlWJ1IBJGqBJmcAljVAlLuASSUUYoOoxQJQyNsJkFljHDZhhIkSFWh4G1Y3UZmGmhFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, #"Cumulative Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Cumulative Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in Table.AddColumn(tab,"New",(x)=>try Table.Max( Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Cumulative Value] otherwise (x)[Cumulative Value] ), type table [Name=nullable text, Date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Data", each Table.AddColumn([Data],"Diff", each [Cumulative Value] - [New])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded New Data" = Table.ExpandTableColumn(#"Removed Columns", "New Data", {"Date", "Cumulative Value", "Diff"}, {"New Data.Date", "New Data.Cumulative Value", "New Data.Diff"}), #"Sorted Rows" = Table.Sort(#"Expanded New Data",{{"New Data.Date", Order.Ascending}, {"Name", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"New Data.Date", "Date"}, {"New Data.Cumulative Value", "Cumulative Value"}, {"New Data.Diff", "Diff"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Cumulative Value", Int64.Type}, {"Diff", Int64.Type}}) in #"Changed Type1"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
You can use the following formula, just replace MyTable with the name of the previous step:
= Table.FromColumns(Table.ToColumns(MyTable) & {List.Accumulate(MyTable[Cumulative Value], [List = {}, Prev = 0], (current, next) => [List = current[List] & {next - current[Prev]}, Prev = next - current[Prev]])[List]}, Table.ColumnNames(MyTable) & {"DoD"})
Hi Anonymous ,
You can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzTVU0lEyMjAy1DfRBzENlWJ1IBJGqBJmcAljVAlLuASSUUYoOoxQJQyNsJkFljHDZhhIkSFWh4G1Y3UZmGmhFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, #"Cumulative Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Cumulative Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in Table.AddColumn(tab,"New",(x)=>try Table.Max( Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Cumulative Value] otherwise (x)[Cumulative Value] ), type table [Name=nullable text, Date=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Data", each Table.AddColumn([Data],"Diff", each [Cumulative Value] - [New])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded New Data" = Table.ExpandTableColumn(#"Removed Columns", "New Data", {"Date", "Cumulative Value", "Diff"}, {"New Data.Date", "New Data.Cumulative Value", "New Data.Diff"}), #"Sorted Rows" = Table.Sort(#"Expanded New Data",{{"New Data.Date", Order.Ascending}, {"Name", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"New Data.Date", "Date"}, {"New Data.Cumulative Value", "Cumulative Value"}, {"New Data.Diff", "Diff"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Cumulative Value", Int64.Type}, {"Diff", Int64.Type}}) in #"Changed Type1"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
