Calculating change between periods

[Anonymous]

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

Period	Beginining Population
Ice Age	1
Renaissance	50
Industrial Revolution	200

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:

Period	Ending Period	Beginining Population	Ending Population	Change
Ice Age	Renaissance	1	50	49
Renaissance	Industrial Revolution	50	200	150
Industrial Revolution	NA	200	NA	NA

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

[changqing]

Hi @Anonymous ,

Provide another way of thinking:

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

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:

Best Regards,
changqing

[Barthel · Accepted solution]

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.