Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 measure that calculates the earliest selected year. So for example if I have 2022 and 2020 selected the first measure will return the total [Net Value Adjusted] for 2022 and the second measure will return the total [Net Value Adjusted] for 2020. Here is my current measure:
I would like to change { 2022 } to a dynamic filter as the latest year selected in my filter then create an identical measure where the dynamic function is the earliest select year.
For reference the measure [Invoiced RT ACT Material Cost] is calculated:
Solved! Go to Solution.
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!!!
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.
@v-xinruzhu-msft I don't know why I didn't think of using a variable! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.