Forum Discussion
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
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_tolleRegular 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-msftMicrosoft Employee
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,