Variance between two measure values

[jawilson808]

Hi All, I am attempting to calculate the difference between multiple forecast scenarios. As an example, from the screeshot below, i am attempting to calculate the difference column based on the prior forecast.  

Forecast period   CY

Forecast		Value	Difference
Forecast 1		28.2
Forecast 2		12.9	-15.3
Forecast 3		28.7	15.8

In attempting to calulate the difference I have used the following dax, but I receive the same "value".

Value - calculate(Value,selectedvalue(forecast)="forecast 1")

This returns only the forecast 2 value as the forecast 1 value returns as 0.

Any assistance would be greatly appreciated.

[jawilson808]

@amitchandak A follow up question to the above. How can I critique this formula for when its drilled down on. As an example, when drilling down on Forecast 2 the difference is equal to it self. I believe this has something thing to do with the "maxx" used in the formula.

[amitchandak · Accepted solution]

@jawilson808 , A new column

new column =
[Value] - Sumx(filter(Table,[forecast]="forecast 1"),[value])

/// or a new measure 
new measure =
sum(Table[Value]) - Maxx(filter(allselected(Table),[forecast]="forecast 1"),[value])

[jawilson808]

Thank you so much @amitchandak. Worked like a charm.