Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
7 REPLIES 7
Anonymous
Not applicable

@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
Not applicable

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
Not applicable

@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
Not applicable

 
 

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
Not applicable

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

Change.PNG

Anonymous
Not applicable

Capture.PNG

Anonymous
Not applicable

@Anonymous  - Use the Date column from the Date table instead of Start Date.

As a general rule, you will want to create a Star Schema and then use the descriptive attributes from the dimension tables in your charts. This isn't necessary for very simple scenarios, but will be helpful to follow this best practice.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.