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 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 numberForecast value
01500
02325
03460
04300
05250
Location numberDateValue
01Jan 202265
01Feb 2022100
02Apr 202220
03May 2022100
04Dec 202150

 

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.

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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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. 

       

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    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.