cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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])))

1 ACCEPTED SOLUTION
Community Support

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 5
Community Support

``````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.

Helper I

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?

Community Support

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.

Helper I

How do I do that?

Super User

Hi @GVallentgoed

Can you share sample file to look at the issue, As per provided information it should work fine.

Regards,
NG

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.