topic Re: Help with Formula to return a value for the max date for each unique ID in DAX Commands and Tips

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

lpost1214 — Mon, 31 Oct 2022 18:43:19 GMT

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!

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

lukiz84 — Tue, 01 Nov 2022 04:31:18 GMT

Hi,

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

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

lpost1214 — Tue, 01 Nov 2022 12:34:41 GMT

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!

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

lukiz84 — Tue, 01 Nov 2022 14:43:45 GMT

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

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

lpost1214 — Tue, 01 Nov 2022 19:47:00 GMT

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.

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

v-zhangti — Wed, 02 Nov 2022 06:07:15 GMT

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.

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

lpost1214 — Wed, 02 Nov 2022 15:12:03 GMT

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?