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

Calculating change between periods

Hi there, I have some data that looks like this:

 

PeriodBeginining Population
Ice Age1
Renaissance 50
Industrial Revolution200

 

My end goal is to calculate the change between periods but I'm struggling to figure out what the correct columns/measures are to calculate that:

 

PeriodEnding PeriodBeginining PopulationEnding PopulationChange
Ice AgeRenaissance15049
Renaissance Industrial Revolution50200150
Industrial RevolutionNA200NANA

 

What would the simplest way to calculate the above? Thanks so much! 

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

Hey,

 

This is best done in Power Query. Here is the code for the query that you can place in the Advanced Editor. The code is exactly based on your example above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXBMT1XSUTJUitWJVgpKzUvMLC5OzEsGiZkagAU981JKi0uKMhNzFIJSy/JzSksy8/OA0kYGQPlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Beginning Population" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Beginning Population", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "NextIndex", each [Index] + 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NextIndex"}, #"Added Custom", {"Index"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Period", "Beginning Population"}, {"Ending Period", "Ending Population"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Expanded Added Custom", "Population Change", each [Ending Population] - [Beginning Population], Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Subtraction",{"Period", "Beginning Population", "Ending Period", "Ending Population", "Population Change"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Period", "Ending Period", "Beginning Population", "Ending Population", "Population Change"})
in
    #"Reordered Columns"

Please let me know if you have any questions.

View solution in original post

2 REPLIES 2
changqing
Resolver II
Resolver II

Hi @Anonymous ,

 

Provide another way of thinking:

1. First, create a new Index column in Power Query Eidtor to indicate the order of Periods.

changqing_0-1660023941305.png

2. Then create 3 measures:

Ending Period = 
VAR _index =
    MAX ( 'Table'[Index] ) + 1
VAR _period =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
        'Table'[Period]
    )
VAR _result =
    IF ( _period <> BLANK (), _period, "NA" )
RETURN
    _result
Ending Population = 
VAR _index =
    MAX ( 'Table'[Index] ) + 1
VAR _population =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
        'Table'[Beginining Population]
    )
VAR _result =
    IF ( _population <> BLANK (), _population, "NA" )
RETURN
    _result
Change = 
VAR _index =
    MAX ( 'Table'[Index] ) + 1
VAR _beg_population = 
MAX('Table'[Beginining Population])
VAR _population =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] = _index ),
        'Table'[Beginining Population]
    )
VAR _change = _population - _beg_population
VAR _result =
    IF ( _population <> BLANK (), _change, "NA" )
RETURN
    _result

3.Result:

changqing_1-1660025304397.png

 

Best Regards,
changqing

Barthel
Solution Sage
Solution Sage

Hey,

 

This is best done in Power Query. Here is the code for the query that you can place in the Advanced Editor. The code is exactly based on your example above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxOVXBMT1XSUTJUitWJVgpKzUvMLC5OzEsGiZkagAU981JKi0uKMhNzFIJSy/JzSksy8/OA0kYGQPlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Beginning Population" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Beginning Population", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "NextIndex", each [Index] + 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NextIndex"}, #"Added Custom", {"Index"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Period", "Beginning Population"}, {"Ending Period", "Ending Population"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Expanded Added Custom", "Population Change", each [Ending Population] - [Beginning Population], Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Subtraction",{"Period", "Beginning Population", "Ending Period", "Ending Population", "Population Change"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Period", "Ending Period", "Beginning Population", "Ending Population", "Population Change"})
in
    #"Reordered Columns"

Please let me know if you have any questions.

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.

Top Solution Authors