Forum Discussion
Forecast vs Prior Forecast variance
Hi
I'm looking to do a varaince between multiple forecasts. We have a new forecast each month and would like to dynamically figure out the varaince between forecasts. Is the only way to create a new measure for each forecast scenario?
I want to do something similar to using DATEADD, where i can easily pick up Prior year revenue but instead of prior year, i get the prior forecast revenue.
I do have a calendar and Forecast Scenario table relationship in the data model.
Hi GKS1204
Here is a calculated column method:
Prior Forecast = VAR _posOfSpace = FIND(" ",'Table'[Forecast]) VAR _curNo = RIGHT('Table'[Forecast],LEN('Table'[Forecast])-_posOfSpace) VAR _client = 'Table'[Client Code] RETURN MAXX(FILTER('Table','Table'[Client Code]=_client&&'Table'[Forecast]="Forecast "&(_curNo-1)),'Table'[Revenue])Here is a measure method:
Variance = VAR _curRevenue = MAX('Table'[Revenue]) VAR _curForecast = MAX('Table'[Forecast]) VAR _curNo = MID(_curForecast,10,LEN(_curForecast)-9) VAR _preRevenue = CALCULATE(MAX('Table'[Revenue]),ALLEXCEPT('Table','Table'[Client Code]),'Table'[Forecast]="Forecast "&(_curNo-1)) RETURN _curRevenue - _preRevenueBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
2 Replies
Hi GKS1204
Here is a calculated column method:
Prior Forecast = VAR _posOfSpace = FIND(" ",'Table'[Forecast]) VAR _curNo = RIGHT('Table'[Forecast],LEN('Table'[Forecast])-_posOfSpace) VAR _client = 'Table'[Client Code] RETURN MAXX(FILTER('Table','Table'[Client Code]=_client&&'Table'[Forecast]="Forecast "&(_curNo-1)),'Table'[Revenue])Here is a measure method:
Variance = VAR _curRevenue = MAX('Table'[Revenue]) VAR _curForecast = MAX('Table'[Forecast]) VAR _curNo = MID(_curForecast,10,LEN(_curForecast)-9) VAR _preRevenue = CALCULATE(MAX('Table'[Revenue]),ALLEXCEPT('Table','Table'[Client Code]),'Table'[Forecast]="Forecast "&(_curNo-1)) RETURN _curRevenue - _preRevenueBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Thank you! I'm just learning VAR don't seem to work in excel, but i'm going to give this a shot in Power BI.
