Help with Formula to return a value for the max date for each unique ID

[lpost1214]

I have historical forecast data for all forecated materials and below is an example of one. I would like to create a formula to return the 'planned qty' for the latest 'date of last change' for each 'key'.  

Once i have this value i am looking to put in a matrix format. i.e. below 202209 it should show 484, 202210 should show 500, etc. 

Thanks in advance! 

[lukiz84 · Accepted solution]

Ah ok, yeah, measure makes sense, should work:

PlannedQty =
   CALCULATE(
      MAX(Forecast[Planned quantity_PLNMG]),
      FILTER(
         Forecast,
         Forecast[Date of Last Change_LAEDA] = MAX(Forecast[Date of Last Change_LAEDA])
      )
   )

[lukiz84 · Accepted solution]

Ah ok, yeah, measure makes sense, should work:

PlannedQty =
   CALCULATE(
      MAX(Forecast[Planned quantity_PLNMG]),
      FILTER(
         Forecast,
         Forecast[Date of Last Change_LAEDA] = MAX(Forecast[Date of Last Change_LAEDA])
      )
   )

[lpost1214]

One more question. I am trying to put this into a matrix where i can view the results by customer, product group, or all of the individual material results from the calculation you have provided.  Currently, if i use the planned qty formula you provided and i collapse from material up one level to customer/brand, i don't get a sum of each of the calculated planned qtys. In the example below, i'd expect the result for 01-Jul to be 1,425, not 150. How can i achieve that? 

[lpost1214]

Thank you, this worked. How would i calculate it as a column? I have a similar scenario that i think may work better by adding a column.

[v-zhangti · Accepted solution]

Hi, @lpost1214 

You can try the following methods.

Column:

Column =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', [Key] = EARLIER ( 'Table'[Key] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Planned] ),
        FILTER ( 'Table', [Key] = EARLIER ( 'Table'[Key] ) && [Date] = _maxdate )
    )

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[lpost1214]

I think i'd like to have it calculated as a measure, but i honestly get confused about when is the correct instance to use each. Thank you!

[lukiz84]

Hi,

do you want it as a calculated column or a measure?