Forum Discussion

GVallentgoed's avatar
GVallentgoed
Helper II
2 years ago
Solved

Goal Blank infinity Error

KPI goal error when calculating previous year.  Trying to display average increase from 2023 - 2024. When calculating the average of 2023, get blank infinity error. Need to fix DAX to only recognize average of 2023, but cant fingure out how to filter.

2023 PLT = IF('TLO (2)'[Latest Pickup].[Year]=2023,CALCULATE(AVERAGE('TLO (2)'[PLT])))





 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GVallentgoed 

     

    Actually, slicers can not affect calculated columns because they're at different levels.

    Slicers are on the report view and calculated columns are on the table view. Slicer can only filter on visuals on report view. The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. 

     

    Consider writing a measure and create a table visual in the report view:

    _2023 PLT =
VAR _currentRowYear =
    YEAR ( MAX ( 'TLO (2)'[Latest Pickup] ) )
RETURN
    IF (
        _currentRowYear = 2023,
        CALCULATE (
            AVERAGE ( 'TLO (2)'[PLT] ),
            FILTER (
                ALLSELECTED ( 'TLO (2)' ),
                YEAR ( 'TLO (2)'[Latest Pickup] ) = _currentRowYear
            )
        )
    )

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GVallentgoed 

     

    Please try this:

    2023 PLT =
IF (
    YEAR ( 'TLO (2)'[Latest Pickup] ) = 2023,
    CALCULATE (
        AVERAGE ( 'TLO (2)'[PLT] ),
        FILTER (
            ALLSELECTED ( 'TLO (2)' ),
            YEAR ( 'TLO (2)'[Latest Pickup] )
                = YEAR ( EARLIER ( 'TLO (2)'[Latest Pickup] ) )
        )
    )
)

    The calculated column can only return the year = 2023, the other year like 2024 return blank.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • GVallentgoed's avatar
      GVallentgoed
      Helper II
      2 years ago

      This solves the blank infinity problem, however, it doesnt work with my filters. The 2023 average should change based on filter.  What needs to be added to formula?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi GVallentgoed 

         

        Actually, slicers can not affect calculated columns because they're at different levels.

        Slicers are on the report view and calculated columns are on the table view. Slicer can only filter on visuals on report view. The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. 

         

        Consider writing a measure and create a table visual in the report view:

        _2023 PLT =
VAR _currentRowYear =
    YEAR ( MAX ( 'TLO (2)'[Latest Pickup] ) )
RETURN
    IF (
        _currentRowYear = 2023,
        CALCULATE (
            AVERAGE ( 'TLO (2)'[PLT] ),
            FILTER (
                ALLSELECTED ( 'TLO (2)' ),
                YEAR ( 'TLO (2)'[Latest Pickup] ) = _currentRowYear
            )
        )
    )

         

         

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.