cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS
Memorable Member

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

Community Support

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.

6 REPLIES 6
Memorable Member

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

Regular Visitor

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?

Regular Visitor

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.

Community Support

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.

Regular Visitor

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!

Memorable Member

Hi,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors