Forum Discussion

Thigs's avatar
Thigs
Helper IV
1 year ago
Solved

Projection based on numeric parameters

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.  ...
  • v-tejrama's avatar
    v-tejrama
    1 year ago

    Hi Thigs ,

    To get more accurate projections, I’d suggest using a dynamic DAX calculation that responds to your input parameter. You can use something like SWITCH() or IF() in your measure to handle different values of the parameter.

    For example:

    Projected Value =
    SWITCH(
    TRUE(),
    [Parameter] = 1, [Value] * 1.1,
    [Parameter] = 2, [Value] * 1.2,
    [Parameter] = 3, [Value] * 1.3,
    [Value]
    )


    This way, the projections will automatically adjust depending on the input selected. It’s a flexible method and easy to maintain if you plan to add more conditions later. Also, just a quick tip if you're working with a large dataset, try to avoid using calculated columns unless necessary. Measures are generally more performance-friendly in such cases.
    Please feel free to come back if you have any queries.

    Thank you.

     

FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

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

Thigs's avatar
Thigs
Helper IV
1 year ago

So, I tried changing the formula to see if we can isolate what is going wrong. It's something with the Year = Go Live Year, because if I just tell it to use Year = 2025, etc, it is working.