Forum Discussion

andy_tolle's avatar
andy_tolle
Regular Visitor
8 years ago
Solved

Multiple years of Financial forecasts: differences?

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

 

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

  • andy_tolle

     

    Firstly, you should unpivot your table like below: 

     

     

    Then you can create calculated column to get  the PreviousYearForcast: 

     

    PreviousYearForecast =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            'Table',
            'Table'[Year]
                = EARLIER ( 'Table'[Year] ) - 1
                && 'Table'[AccountNumber] = EARLIER ( 'Table'[AccountNumber] )
                && 'Table'[BudgetYear] = EARLIER ( 'Table'[BudgetYear] )
        )
    )

    Now you just need to use Forecast column minus PreviousYearForecast to get the difference: 

     

    Difference = IF([PreviousYearForecast]=BLANK(),BLANK(),'Table'[Forecast]-'Table'[PreviousYearForecast])

     

    Regards,

2 Replies

  • andy_tolle's avatar
    andy_tolle
    Regular Visitor

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

     

    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

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee

      andy_tolle

       

      Firstly, you should unpivot your table like below: 

       

       

      Then you can create calculated column to get  the PreviousYearForcast: 

       

      PreviousYearForecast =
      CALCULATE (
          SUM ( 'Table'[Forecast] ),
          FILTER (
              'Table',
              'Table'[Year]
                  = EARLIER ( 'Table'[Year] ) - 1
                  && 'Table'[AccountNumber] = EARLIER ( 'Table'[AccountNumber] )
                  && 'Table'[BudgetYear] = EARLIER ( 'Table'[BudgetYear] )
          )
      )

      Now you just need to use Forecast column minus PreviousYearForecast to get the difference: 

       

      Difference = IF([PreviousYearForecast]=BLANK(),BLANK(),'Table'[Forecast]-'Table'[PreviousYearForecast])

       

      Regards,