Multiple years Financial forecasts: differences?

[andy_tolle]

I have a table that contains a bunch of financial forecasts for multiple years. For example purposes, I simplified it:

Note for example that:

• In the forecast of 2016, for the account number "61", we predicted a budget of 1.200,- for Year+1 (so for the year 2017)
• In the forecast of 2017, for that same account number "61", we predict that same 1.200,- for the budget of that year (so for the year 2017)
• So in this case, the "forecast_of_2016" predicts the same amount for 2017 as the "forecast_of_2017" predicts.

What I would like to do, is find the differences between forecasts. For example:

Note how:

• In the "forecast_of_2016", for the account number "61", we predicted a budget of 1.400,- for year+2 (so for the year 2018)
• In the "forecast_of_2017", for the same account number "61", we predicted a budget of 1.500,- for the year+1 (so for the year 2018)
• So in this case, the "forecast_of_2016" predicts a different amount for 2018 as the "forecast_of_2018". I'd like to make a report that shows these differences. I'm looking for some help to get the model right.

Maybe an important remark:

• Note how some account numbers are in the "forecast_of_2016" (e.g. "62"), but not in the "forecast_of_2017". That should also mark as a difference
• Similar: some account numbers are in the "forecast_of_2017" (e.g. "63"), but not in the  "forecast_of_2016". This too should also be mark as a difference

I'd appreciate if you could help me with this. Thanks for reading.

Kind regards,

Andy

[mattbrice · Accepted 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.