Calculate difference between dates(months)

[thomma]

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!

[dufoq3 · Accepted solution]

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