cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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!

1 ACCEPTED SOLUTION
Super User

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``````

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Super User

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``````

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.