Forum Discussion
Multiple years Financial forecasts: differences?
- 8 years ago
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_budgetI'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_budgetI'm sure you could do as a measure if needed, but i'll leave that to you. Good luck.