Forum Discussion
Solved
Calculate based on selected filter
I have a measure that returns the total [Net Value Adjusted] for 2022 based on [Invoice Created Date]. I want to change this measure to calulate the latest selected year and create another separate m...
Hi bstark1287
You can refer to the following example.
Invoiced RT CY Act Material Cost_2022= VAR _maxperYear = CALCULATE(YEAR(MAX('Raw Order Data'[Invoice Created Date])), VALUES('Raw Order Data'[Invoice Created Date].[Year])) RETURN CALCULATE( [Invoiced RT ACT Material Cost], YEAR('Raw Order Data'[Order Invoiced Date]) =_maxperYear )Invoiced RT CY Act Material Cost_2021= VAR _minperYear = CALCULATE(YEAR(MIN('Raw Order Data'[Invoice Created Date])), VALUES('Raw Order Data'[Invoice Created Date].[Year])) RETURN CALCULATE([Invoiced RT ACT Material Cost],YEAR('Raw Order Data'[Order Invoiced Date])=_minperYear ）Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I got some measures working but not how I intended. Basically the MAX measure will return the higher value. So if 2021 had higher sales total than 2022 it will return the 2021 sales value. Similarly MIN returns the lower volume not the lower year. Any help is greatly appreciated!!!
Invoiced RT ACT Material Cost max per Year =
MAXX(
KEEPFILTERS(VALUES('Raw Order Data'[Invoice Created Date].[Year])),
CALCULATE([Invoiced RT ACT Material Cost])
)
Invoiced RT ACT Material Cost min per Year =
MINX(
KEEPFILTERS(VALUES('Raw Order Data'[Invoice Created Date].[Year])),
CALCULATE([Invoiced RT ACT Material Cost])
)
Hi bstark1287
You can refer to the following example.
Invoiced RT CY Act Material Cost_2022=
VAR _maxperYear =
CALCULATE(YEAR(MAX('Raw Order Data'[Invoice Created Date])),
VALUES('Raw Order Data'[Invoice Created Date].[Year]))
RETURN
CALCULATE( [Invoiced RT ACT Material Cost], YEAR('Raw Order Data'[Order Invoiced Date]) =_maxperYear )Invoiced RT CY Act Material Cost_2021=
VAR _minperYear =
CALCULATE(YEAR(MIN('Raw Order Data'[Invoice Created Date])),
VALUES('Raw Order Data'[Invoice Created Date].[Year]))
RETURN
CALCULATE([Invoiced RT ACT Material Cost],YEAR('Raw Order Data'[Order Invoiced Date])=_minperYear ）
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous I don't know why I didn't think of using a variable! Thank you!