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.

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Make sure both tables are joined with the common location table

 

Sum(Table[Forecast Value]) - Sum(Table2[Value])

Should work

 

or

 

sumx(Values('Date'[Month Year]), calculate(Sum(Table[Forecast Value]) - Sum(Table2[Value]) ) )

 

best it to have crossjoin with distinct monthstart date and join with date table

 

new Table= crossjoin([Table], distinct('Date'[Month Start Date]) )

 

 

again the first measure will work

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak thanks for the response.

     

    I tried both methods but I got the same difference value for each location number which is not what I want.