Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate difference between forecast (constant) and monthly values

Hi There,  I have two tables; one contains the forecast values and the other contains values that increases with time (monthly). I want to subtract the maximum value for each record from the forec...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    You can try this measure,

    diff = 
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=MAX('Table'[Location number])),[Forecast value])
return _forecast-MAX('Table'[Value])

    if you need calculated column, try this,

    Column = 
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=EARLIER('Table'[Location number])),[Forecast value])
return _forecast-'Table'[Value]

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

v-xiaotang thanks for the response. 

This method works however I have a little issue. Some of the location number have alphanumeric IDs. Is there a way to go around this?

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous 

If you use my measure, please make sure that the columns in the blue box are of the same type, either numeric or textual

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.