topic Calculating change between periods in DAX Commands and Tips

Calculating change between periods

Anonymous — Tue, 02 Aug 2022 19:01:42 GMT

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!

Re: Calculating change between periods

Barthel — Tue, 02 Aug 2022 20:02:27 GMT

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.

Re: Calculating change between periods

changqing — Tue, 09 Aug 2022 06:09:51 GMT

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