Skip to main content
cancel
Showing results for 
Search instead 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

Reply
thomma
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.

 

thomma_0-1718284554569.png

 

What would be the best way to do it? The data is also grouped by project. Thanks in advance!

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @thomma, like this?

 

Result:

dufoq3_0-1718287456669.png

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.

View solution in original post

1 REPLY 1
dufoq3
Super User
Super User

Hi @thomma, like this?

 

Result:

dufoq3_0-1718287456669.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors