Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How do you calculate the incremental change between rows?

How would you calaculate the difference between rows based on a certain criteria shared between the rows? In this case a Power Station's name, and then its increase in capacity over time. I would lik...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous   - You could take a look at this blog for a DAX solution.

I added a variation on the solution for Power Query in the comment section, and have modified that script to fit your scenario. Note that I modified the date format in the source to MM/DD/YYYY.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY87C8MgFEb/ijgL916NecydO6XQITikrZBAMGD0/9cHXe1yp3MO310Wfl/de7NXsJ7NZwybYI/oX7uzXHCNmC4qQAKJJLkRbaGvQg+ks6D+CoPOAqV8EYYizPaMB7tZF/x6CPbc3SdBsrYJcEooTg1UlSp2QGOuUgPt6gAJqryoG+jwGyBVRkduzBc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Power Station" = _t, #"Total Capacity" = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Power Station", type text}, {"Total Capacity", Int64.Type}, {"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Power Station", Order.Ascending}, {"Start Date", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(#"Sorted Rows", "Smart Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddIndex, "Prev Index", each [Smart Index] - 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Prev Index"}, #"Added Custom", {"Smart Index"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Power Station", "Total Capacity"}, {"Previous Power Station", "Previous Total Capacity"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Total Capacity Difference", each if [Power Station] = [Previous Power Station] then [Total Capacity] - [Previous Total Capacity] else [Total Capacity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Prev Index", "Previous Power Station", "Previous Total Capacity"})
in
    #"Removed Columns"

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Can I do it without making an index as the sort order will constantly be changing. Is there a way of looking up the plant name, on the previous date and taking it's capacity away from the plant with the most recent date?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  - 

    For dynamic results, we need to use a DAX Measure.

    Please see attached. The measure that calculates the difference is:

    Capacity Change = 
IF(
    ISBLANK([Capacity On Date]),
    BLANK(),
    var _prev_date = LASTNONBLANK(
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] < SELECTEDVALUE('Date'[Date])
        ),
        [Capacity On Date]
    )
    return 
        [Capacity On Date] -
        CALCULATE(
            [Capacity On Date],
            'Date'[Date] = _prev_date
        )
)
    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
       
       

      Thanks for that. It is nearly there, but when you put the data in to a chart it shows the original capacity not the change in capacity

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous - Could you show the problematic chart? The example pbix appears to be working properly in a table visual: