Calculate difference between forecast (constant) and monthly values

[Anonymous]

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 forecast values. How do I do this in DAX? The table is linked by the location number (unique ID). The forecast values are constant for each location.

Sample data below for the forecast and varing data.

Location number	Forecast value
01	500
02	325
03	460
04	300
05	250

Location number	Date	Value
01	Jan 2022	65
01	Feb 2022	100
02	Apr 2022	20
03	May 2022	100
04	Dec 2021	50

Example the difference for location 01 in Jan 2022 is 500 - 65 = 435. In Feb 2022 it's 400. I want this for all location numbers. I need this to information in my report to know how much of the forecast is remaining. 

Also, how do I sort my legend in ascending order? See below

Any help will be greatly appreciated.

[v-xiaotang · Accepted solution]

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]

@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]

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.

[Anonymous]

I got an error when I tried the formula.

[amitchandak]

@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]

@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.