Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table that contains a bunch of financial forecasts for multiple years. For example purposes, I simplified it:
Note for example that:
What I would like to do, is find the differences between forecasts. For example:
Note how:
Maybe an important remark:
I'd appreciate if you could help me with this. Thanks for reading.
Kind regards,
Andy
Solved! Go to Solution.
I would use power query to unpivot the "Budget Year", "Budget Year + 1" & "Budget Year +2" columns, then rename them to [Year], [Year] + 1, [Year] +2 respectively and convert to whole numbers. Then use a dax calculated column or measure to compute the numbers you want.
Here is Power Query I generated:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Account", Int64.Type}, {"Budget Year", type number}, {"Budget Year + 1", type number}, {"Budget Year + 2", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Account"}, "Attribute", "Value"), #"Added Conditional Column1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "Budget Year" then [Year] else if [Attribute] = "Budget Year + 1" then [Year] + 1 else [Year] + 2 ), #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Budget Year"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Budget Year", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Year", "Budget Year", "Account", "Value"}) in #"Reordered Columns"
then a dax calculated column:
= VAR this_year_value = Table1[Value] VAR this_year_budget_year = Table1[Budget Year] VAR Next_year = Table1[Year] + 1 VAR next_year_budget = CALCULATE ( MAX ( Table1[Value] ), FILTER ( ALLEXCEPT ( Table1, Table1[Account] ), Table1[Year] = Next_year && Table1[Budget Year] = this_year_budget_year ) ) RETURN this_year_value - next_year_budget
I'm sure you could do as a measure if needed, but i'll leave that to you. Good luck.
I would use power query to unpivot the "Budget Year", "Budget Year + 1" & "Budget Year +2" columns, then rename them to [Year], [Year] + 1, [Year] +2 respectively and convert to whole numbers. Then use a dax calculated column or measure to compute the numbers you want.
Here is Power Query I generated:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Account", Int64.Type}, {"Budget Year", type number}, {"Budget Year + 1", type number}, {"Budget Year + 2", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year", "Account"}, "Attribute", "Value"), #"Added Conditional Column1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "Budget Year" then [Year] else if [Attribute] = "Budget Year + 1" then [Year] + 1 else [Year] + 2 ), #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Budget Year"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Budget Year", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Year", "Budget Year", "Account", "Value"}) in #"Reordered Columns"
then a dax calculated column:
= VAR this_year_value = Table1[Value] VAR this_year_budget_year = Table1[Budget Year] VAR Next_year = Table1[Year] + 1 VAR next_year_budget = CALCULATE ( MAX ( Table1[Value] ), FILTER ( ALLEXCEPT ( Table1, Table1[Account] ), Table1[Year] = Next_year && Table1[Budget Year] = this_year_budget_year ) ) RETURN this_year_value - next_year_budget
I'm sure you could do as a measure if needed, but i'll leave that to you. Good luck.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |