Limiting Value of a New Measure

[jms1425]

Good morning,

I have a dashboard that displays a weekly rate of completion of a task for the prior year, and I want to display a goal for the current year, based on the prior year. If the goal is to increase by 10%, but last year's value is >90%, the goal is >100% Is there a way to limit the maximum value of the goal?

Thanks!

[ssugar · Accepted solution]

after you calculate your current year goal, you should be able to use an if statement.  Something like:

adjustedcurrentyeargoal = if([currentyeargoal] > 1, 1, [currentyeargoal])

this assumes your currentyeargoal measure is formatted as a percent, and the value is actually between 0 and 1

let me know if you need any further clarification

[ssugar · Accepted solution]

after you calculate your current year goal, you should be able to use an if statement.  Something like:

adjustedcurrentyeargoal = if([currentyeargoal] > 1, 1, [currentyeargoal])

this assumes your currentyeargoal measure is formatted as a percent, and the value is actually between 0 and 1

let me know if you need any further clarification

[jms1425]

Ah, yes - create a new measure for the goal, then create 2nd new measure with the IF statement, and display the 2nd measure. It seems so obvious now!

Thanks!

[ssugar · Accepted solution]

No worries.  You could also wrap it all into one DAX statement.  Something like this:

CurrentYearGoal = 
var __currentyeargoal = [your calculation to get the current year goal]
return if([__currentyeargoal] > 1, 1, [__currentyeargoal])