Forum Discussion
Forecast graph based on user input
hello some_analyst
for your first post, input is a table that consisted of user input as your post above.
i am not sure but i assumed you have a way for user to write down their input.
User input should be in a form of table in PBI so you can get that dynamic value into calculation.
the input table in my pbix is a dummy table, so you need to change that table into user input table.
for your second post, in your screenshot, i assumed the negatif value is the problem and i assumed you put in 20 as input.
this probably happens because the value is zero instead of blank in 28 April 2025, so the value becomes negative (0-20=-20).
if this the problem, then lets make a tweak in the DAX by adding not equal to 0.
Pending Forecast =
var _MaxDate =
CALCULATE(
MAX('dim_date'[Week Start]),
FILTER(
ALL('Dummy'),
not ISBLANK('Dummy'[Pending])&&
'Dummy'[Pending]<>0
)
)
var _MaxValue =
MAXX(
FILTER(
ALL('Dummy'),
'Dummy'[Date]=_MaxDate
),
'Dummy'[Pending]
)
var _Input = SELECTEDVALUE('What-If-Parameter'[What-If-Parameter])
Return
IF(
SELECTEDVALUE('Dummy'[Pending])<>BLANK(),
SELECTEDVALUE('Dummy'[Pending]),
_MaxValue -_Input
)this should take the 1096 value as last value then deducted with 20 in 28 April 2025.
Hope this will help.
Thank you.
Hi some_analyst ,
Thank you for reaching out to the Microsoft Community Forum.
Can you please follow below steps:
1. Create a What-If Parameter for Input (Reduction/Week):
Go to Modeling > New Parameter and call it Weekly Decrease.
Data type: Whole Number, Minimum: 0, Maximum: 100 , Increment: 1 and Default: 35
Power BI will create a slicer automatically so the user can select different values (e.g., 35, 45).
2. Build a Forecast Column (Calculated Column):
In your table (e.g., PendingApplications), create a new column:
Pending Forecast by Input =
VAR StartWeek = MINX(FILTER(PendingApplications, NOT(ISBLANK([Pending]))), [Week Start])
VAR StartPending =
CALCULATE(
MAX([Pending]),
FILTER(PendingApplications, [Week Start] = StartWeek)
)
VAR WeeksPassed =
DATEDIFF(StartWeek, [Week Start], WEEK)
VAR DecreasePerWeek = SELECTEDVALUE('Weekly Decrease'[Weekly Decrease])
VAR ForecastValue = StartPending - (WeeksPassed * DecreasePerWeek)
RETURN IF(ForecastValue >= 0, ForecastValue, 0)
This will dynamically calculate the forecast each week based on the selected reduction per week.
3. Plot the Forecast Line in Your Graph:
Add a line or combo chart with: X-axis: Week Start, Y-axis: both [Pending] and [Pending Forecast by Input]
Use the slicer from your What-If parameter to test different scenarios (e.g., reduce 35 per week vs. 45)
4. Add a Target Line (e.g., 400)
To show when the forecast crosses the 400 mark:
Create a constant line at 400 (Analytics pane) or create a target measure.
You can also use a DAX measure to calculate when the forecast would reach 400.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
Hi some_analyst ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi some_analyst ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you