Forum Discussion
Solved
Calculate difference between dates(months)
Hi all,
I have a dataset structured as the image below. I want to calculate the difference between each month(date) for columns Spent and POC * Estimated total revenue. So substracting the values for the newest date from the date before. For example, for date 1-1-24 the value for Spent should be 59839.04 - 59759.04 = 80 and 70306.75085 - 70212.75636 = 93.99449 for POC * Estimated total revenue.
What would be the best way to do it? The data is also grouped by project. Thanks in advance!
Hi thomma, like this?
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7JDQQhDARz4W0kH+DjuZvGiPzTGDOsvJp+tVQF7utqnwZNqBN3lqwzbAbgyGrIxGBTRduCY6bYeTyeS3mCmh76LE9KNDUfILsGhjoQSXCJHJ2PSMRkDmG7CwciBPk5/X2PJETcGzKF6yDxAzXzevuj86GWKfrfwOfjyLS1bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Date = _t, Spent = _t, #"POC * Estimated total revenue" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Spent", type number}, {"POC * Estimated total revenue", type number}}, "sk-SK"), fn_diff = (myTable as table)=> let tbl = myTable, BufferedColumns = Table.Buffer(Table.SelectColumns(tbl,{"Spent", "POC * Estimated total revenue"})), GernerateDifferences = List.Generate( ()=> [ x = 0, s = BufferedColumns{x}[Spent], p = BufferedColumns{x}[#"POC * Estimated total revenue"] ], each [x] <= Table.RowCount(BufferedColumns)-1, each [ x = [x]+1, s = BufferedColumns{x}[Spent] - BufferedColumns{[x]}[Spent], p = BufferedColumns{x}[#"POC * Estimated total revenue"] - BufferedColumns{[x]}[#"POC * Estimated total revenue"] ], each [Spent diff = [s], POC diff = [p] ] ), ToTable = Table.FromRecords(GernerateDifferences, type table[Spent diff=number, POC diff=number]), MergedTables = Table.FromColumns(Table.ToColumns(tbl) & Table.ToColumns(ToTable), Value.Type(tbl & ToTable)) in MergedTables, GroupedRows = Table.Group(ChangedType, {"Project"}, {{"All", fn_diff, type table}}), CombinedAll = Table.Combine(GroupedRows[All]) in CombinedAll
1 Reply
Hi thomma, like this?
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7JDQQhDARz4W0kH+DjuZvGiPzTGDOsvJp+tVQF7utqnwZNqBN3lqwzbAbgyGrIxGBTRduCY6bYeTyeS3mCmh76LE9KNDUfILsGhjoQSXCJHJ2PSMRkDmG7CwciBPk5/X2PJETcGzKF6yDxAzXzevuj86GWKfrfwOfjyLS1bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Date = _t, Spent = _t, #"POC * Estimated total revenue" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Spent", type number}, {"POC * Estimated total revenue", type number}}, "sk-SK"), fn_diff = (myTable as table)=> let tbl = myTable, BufferedColumns = Table.Buffer(Table.SelectColumns(tbl,{"Spent", "POC * Estimated total revenue"})), GernerateDifferences = List.Generate( ()=> [ x = 0, s = BufferedColumns{x}[Spent], p = BufferedColumns{x}[#"POC * Estimated total revenue"] ], each [x] <= Table.RowCount(BufferedColumns)-1, each [ x = [x]+1, s = BufferedColumns{x}[Spent] - BufferedColumns{[x]}[Spent], p = BufferedColumns{x}[#"POC * Estimated total revenue"] - BufferedColumns{[x]}[#"POC * Estimated total revenue"] ], each [Spent diff = [s], POC diff = [p] ] ), ToTable = Table.FromRecords(GernerateDifferences, type table[Spent diff=number, POC diff=number]), MergedTables = Table.FromColumns(Table.ToColumns(tbl) & Table.ToColumns(ToTable), Value.Type(tbl & ToTable)) in MergedTables, GroupedRows = Table.Group(ChangedType, {"Project"}, {{"All", fn_diff, type table}}), CombinedAll = Table.Combine(GroupedRows[All]) in CombinedAll