Forecast vs Prior Forecast variance

[GKS1204]

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.  

[Anonymous · Accepted solution]

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!

[GKS1204]

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.