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 II

## Measure to calculate value based on filter

Hello,

I'm trying to create a measure to calculate a value that is filtered to current year that I can use in a line chart to see results over a 52 week period.

This is not working:

CURRENT_YEAR_CALCULATION =
VAR CUR_YR = MAX('DATA'[YEAR_NBR])
VAR CALC = CALCULATE(
DIVIDE('DATA'[NUMERATOR], 'DATA'[DENOMINATOR]),
'DATA'[YEAR_NBR] = CUR_YR
)
RETURN CALC

However, when I hard code the year it works just fine:

CURRENT_YEAR_CALCULATION =
VAR CUR_YR = 2024
VAR CALC = CALCULATE(
DIVIDE('DATA'[NUMERATOR], 'DATA'[DENOMINATOR]),
'DATA'[YEAR_NBR] = CUR_YR
)
RETURN CALC

I'd like to accomplish this so I don't have to hard code the year.  I checked the logic for the CUR_YR variable by creating a separate measure and it is producing 2024, not sure why the calculation isn't recognizing that.
1 ACCEPTED SOLUTION
Super User

@jdwalker5 , Try like

CURRENT_YEAR_CALCULATION =
VAR CUR_YR = MAXX(allselected('DATA'), 'DATA'[YEAR_NBR])
VAR CALC = CALCULATE(
DIVIDE('DATA'[NUMERATOR], 'DATA'[DENOMINATOR]),
'DATA'[YEAR_NBR] = CUR_YR
)
RETURN CALC

2 REPLIES 2
Super User

@jdwalker5 , Try like

CURRENT_YEAR_CALCULATION =
VAR CUR_YR = MAXX(allselected('DATA'), 'DATA'[YEAR_NBR])
VAR CALC = CALCULATE(
DIVIDE('DATA'[NUMERATOR], 'DATA'[DENOMINATOR]),
'DATA'[YEAR_NBR] = CUR_YR
)
RETURN CALC

Helper II

That's the ticket!  Thanks for you your help @amitchandak.

FYI, I was able to get it to work by creating a custom column using MAX('DATE'[YEAR]), but I prefer to use your solution.

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors