Forum Discussion

bstark1287's avatar
bstark1287
Helper II
3 years ago
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 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:

Invoiced RT CY Act Material Cost =
    CALCULATE(
     [Invoiced RT ACT Material Cost],
     'Raw Order Data'[Order Invoiced Date].[Year] IN { 2022 }
 )

 

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:


Invoiced RT ACT Material Cost =
IF(
    ISFILTERED('Raw Order Data'[Order Invoiced Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(
        SUM('Raw Order Data'[Net Value Adjusted]),
        'Raw Order Data'[Order Invoiced Date].[Date]
    )
)

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

3 Replies

  • bstark1287's avatar
    bstark1287
    Helper II
    3 years ago

    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])
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

       

      • bstark1287's avatar
        bstark1287
        Helper II
        3 years ago

        Anonymous I don't know why I didn't think of using a variable! Thank you!