Forum Discussion
Projection based on numeric parameters
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.
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
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.
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 TeamThis 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.
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.
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.