Forum Discussion

andy_tolle's avatar
andy_tolle
Regular Visitor
8 years ago
Solved

Multiple years Financial forecasts: differences?

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 accoun...
  • mattbrice's avatar
    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_budget

    I'm sure you could do as a measure if needed, but i'll leave that to you.  Good luck.