topic Re: Projection based on numeric parameters in DAX Commands and Tips

Projection based on numeric parameters

Thigs — Fri, 25 Jul 2025 19:09:04 GMT

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

)

Re: Projection based on numeric parameters

FBergamaschi — Fri, 25 Jul 2025 19:21:04 GMT

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

Re: Projection based on numeric parameters

Thigs — Fri, 25 Jul 2025 19:33:16 GMT

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.

Re: Projection based on numeric parameters

FBergamaschi — Fri, 25 Jul 2025 19:51:26 GMT

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

Re: Projection based on numeric parameters

Thigs — Fri, 25 Jul 2025 19:58:50 GMT

Re: Projection based on numeric parameters

Thigs — Fri, 25 Jul 2025 20:00:29 GMT

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.

Re: Projection based on numeric parameters

FBergamaschi — Sat, 26 Jul 2025 15:09:02 GMT

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

Re: Projection based on numeric parameters

v-tejrama — Mon, 28 Jul 2025 11:47:02 GMT

Hi Thigs ,

Please follow these steps to achieve the desired outcome:

1. Create a Parameter Table (Disconnected Table)

ProjectionParams =
DATATABLE(
    "Parameter", STRING,
    "Value", DOUBLE,
    {
        {"StartValue", 1000},
        {"GrowthRate", 0.10},
        {"DeclineRate", 0.05},
        {"GrowthMonths", 5},
        {"DeclineMonths", 3},
        {"TotalMonths", 12}
    }
)

2. Build a Calendar Table with a Month Index
MonthIndex =
ADDCOLUMNS(
CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31)),
"MonthNum", DATEDIFF(DATE(2025, 1, 1), [Date], MONTH) + 1
)
This will generate 12 months, with the MonthNum column starting at 1.

3. Define a Measure for Projected Value Calculation
ProjectedValue =
VAR StartValue = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "StartValue")
VAR GrowthRate = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "GrowthRate")
VAR DeclineRate = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "DeclineRate")
VAR GrowthMonths = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "GrowthMonths")
VAR DeclineMonths = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "DeclineMonths")
VAR TotalMonths = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "TotalMonths")
VAR MonthNum = SELECTEDVALUE('MonthIndex'[MonthNum])
VAR Result =
    SWITCH(
        TRUE(),
        MonthNum = 1, StartValue,
        MonthNum <= GrowthMonths + 1, StartValue * POWER(1 + GrowthRate, MonthNum - 1),
        MonthNum <= GrowthMonths + DeclineMonths + 1,
            VAR GrowthEndValue = StartValue * POWER(1 + GrowthRate, GrowthMonths)
            VAR DeclineStep = MonthNum - GrowthMonths - 1
            RETURN GrowthEndValue * POWER(1 - DeclineRate, DeclineStep),
        BLANK()
    )
RETURN Result

4. Visualize the Results
Create a line chart or table with the following:
X-Axis: MonthIndex[Date]
Y-Axis/Values: ProjectedValue (the measure above)
This visualization will illustrate the projected curve based on the growth and decline parameters set.

I’ve attached the .pbix file and screenshots for your reference.

Best Regards,
Tejaswi.
Community Support

Re: Projection based on numeric parameters

Thigs — Mon, 28 Jul 2025 12:46:15 GMT

Hi! Thanks for the assistance, unfortunately that's still not working. I tried the table - it is blank.

Re: Projection based on numeric parameters

Thigs — Mon, 28 Jul 2025 12:53:39 GMT

But this won't work with my existing formula to find the cost, correct? I don't see the current cost anywhere in there. That formula has many pieces, so I need to be able to reference that measure.

Re: Projection based on numeric parameters

Thigs — Mon, 28 Jul 2025 20:00:25 GMT

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.

Re: Projection based on numeric parameters

v-tejrama — Wed, 30 Jul 2025 05:00:36 GMT

Hi Thigs ,

You’re absolutely right the example I provided uses a static StartValue from the parameter table and doesn’t reference your [Total Year 1 Costs] formula.

To incorporate your measure, simply update the measure by replacing this line:

VAR StartValue = CALCULATE(MAX(ProjectionParams[Value]), ProjectionParams[Parameter] = "StartValue")

with:

VAR StartValue = [Total Year 1 Costs]

This change ensures the projection logic is applied directly to your calculated cost. Make sure your [Total Year 1 Costs] measure returns accurate, context-dependent results, especially when filters like category, year, or month are involved. That way, your projections will stay precise.

Best regards,
Tejaswi.
Community Support Team

Re: Projection based on numeric parameters

Thigs — Tue, 29 Jul 2025 15:32:16 GMT

This is getting SO close! My only issues is that it is showing a very set pattern of increase and decline. I do not want it to decline at all. I want it to increase based on the growth projected each year on a slicer, so that the business user can select 5%, 7%, 10%, etc. I also need it to be over years, not months, but I think if I change the months to years that should work.

Re: Projection based on numeric parameters

Thigs — Tue, 29 Jul 2025 21:16:58 GMT

Hi! I still need a bit more help. You see, I don't want any decline, and I want the business user to be able to input what year 1 would be. So it can't be set in the original data, it needs to be flexible.

Re: Projection based on numeric parameters

v-tejrama — Wed, 30 Jul 2025 06:56:33 GMT

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.

Re: Projection based on numeric parameters

v-tejrama — Mon, 04 Aug 2025 05:09:02 GMT

Hi Thigs ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

Re: Projection based on numeric parameters

v-tejrama — Thu, 07 Aug 2025 11:55:59 GMT

Hi Thigs ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Thank you.

Re: Projection based on numeric parameters

v-tejrama — Mon, 11 Aug 2025 08:31:34 GMT

Hi Thigs ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.