Re: Projection based on numeric parameters

Thigs

Hi all!

I've been charged with creating a flexible business case tool, where the business users can input some items, get a cost, use a growth rate, and see how much it would cost year over year.

I have the formula to get the total original cost, which I have called [Total Year 1 Costs]. I also have a numeric parameter that allows the user to select a growth rate (between 0.01 - 0.5) for the annual growth rate. I'm trying to allow them to also select the start year for the project ("Go Live Year"). The individual variables are working, but my switch formula is not. Any assistance is greatly appreciated!

Growing the Cost =

VAR Growth = (1+SELECTEDVALUE('Annual Growth Rate'[Annual Growth Rate]))

VAR BaseYear = [Total Year 1 Costs]

VAR Year1 = [Total Year 1 Costs]*Growth

VAR Year2 = Year1*Growth

VAR Year3 = Year2*Growth

VAR Year4 = Year3*Growth

VAR Year5 = Year4*Growth

VAR Year6 = Year5*Growth

VAR Year7 = Year6*Growth

VAR Year8 = Year7*Growth

VAR Year9 = Year8*Growth

VAR Year10 = Year9*Growth

VAR Year11 = Year10*Growth

RETURN

SWITCH(TRUE(),

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year]), BaseYear,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+1, Year1,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+2, Year2,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+3, Year3,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+4, Year4,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+5, Year5

)

FBergamaschi

I agree, the problem must be here

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year]), BaseYear,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+1, Year1,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+2, Year2,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+3, Year3,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+4, Year4,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year])+5, Year5

I assume the SELECTEDVALUE part is returning a blank as it finds more values, please turn it into

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1), BaseYear,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1)+1, Year1,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1)+2, Year2,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1)+3, Year3,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1)+4, Year4,

MAX(Years[Years]) = SELECTEDVALUE('Go Live Year'[Go Live Year], -1)+5, Year5

and try again, I expect some values now. But please turn the graph into a table, I want to see nrs

To understand why SELECTEDVALUE does not find a single value, in case my intuition is correct, I need to see the tables and the model

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

FBergamaschi

Can you provide some images of what you mean by "not working"?

Thigs

No data is appearing within the graph. I am using a line chart, using Years as my X-axis (not "Go Live" year, but a year table that has one column, Years, from 2025 - 2100). I then pull the measure into the Y axis and nothing occurs.

FBergamaschi

I need to see columns grouped in rows amd columns section of the visual, the selection on slicers, need to see the data model

Without contexts and Data model I cannot understand the issue

Thigs

I've attached three images. The top is my visual, you can see that it is blank. The Total Year 1 Costs formula IS giving a result (it's in the box), and when I return any variable, that is giving me a correct result, so it is an issue with the SWITCH statement at the end.

There isn't really any raw data within this file - it is all based on numeric parameters and formulas.