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 like to have a row showing the original capacity and then any incremantal capacity additions at later dates also in the same column

 

Power StationTotal CapacityStart DateNew Capacity Online (Incremental)
Manchester South, Turbine50001/03/2012500
Manchester South, Turbine60015/06/2013100
Manchester South, Turbine75025/11/2017150
Seoul Central, Wind20009/01/2009200
Seoul Central, Wind35018/04/2011150
Seoul Central, Wind45031/12/2015100
Seoul Central, Wind70023/01/2018250
Need Help

7 Replies

  • 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.