Forum Discussion
Calculating change between periods
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!
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.
2 Replies
- Barthel
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.
changqingResolver 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.
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 _resultEnding 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 _resultChange = 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 _result3.Result:
Best Regards,
changqing