Forum Discussion
Solved
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...
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!
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 - _preRevenue
Best 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.
