Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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])
)
)
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.
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])
)
)
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?
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.
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.
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!
Hi,
do you want it as a calculated column or a measure?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
29 | |
27 | |
20 | |
15 | |
14 |