Forecast graph based on user input

[some_analyst]

I am wondering it this is even doable.

I have a graph where I have all pending applications and I also have a forecast. However, my client wants to see what the forecast would look like (trend) if they had a static decrease of pending applications, so right now there are 1000 and if they clear 35 (meaning input), when in time they would get to 400, vs if they input (reduce) 45.

Is this even possible to do it like that?

My data now is like this:

Week Start	Pending	Pending - Forecast
03/03/2025	1000
10/03/2025	1097
17/03/2025	1055
24/03/2025	1025
31/03/2025	1001
07/04/2025	980
14/4/2025		=same as last week - [Input]
21/4/2025		week before - [Input]
28/4/2025		week before - [Input]
05/05/2025		week before - [Input]

Input is user input and dynamic. Is it even possible?

Thanks a lot for help

[v-dineshya]

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

[v-dineshya]

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 you

[Irwan]

hello @some_analyst 

please check if this accomodate your need.

if yes, then create a new measure with following DAX

Pending Forecast =
var _MaxDate = 
CALCULATE(
    MAX('Table'[Week Start]),
    FILTER(
        ALL('Table'),
        not ISBLANK('Table'[Pending])
    )
)
var _MaxValue = 
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Week Start]=_MaxDate
    ),
    'Table'[Pending]
)
var _Input = SELECTEDVALUE('Input'[Input])
Return
IF(
    SELECTEDVALUE('Table'[Pending])<>BLANK(),
    SELECTEDVALUE('Table'[Pending]),
    _MaxValue-_Input
)

also i made another dummy table for Input because i assumed you have another table for your user to write their Input. if so, then replace the dummy Input table with your Input table.

Hope this will help.

Thank you,

[some_analyst]

Hi @Irwan , I figure out the input with a parameter. But I am having issues with the formula.

Pending Forecast = 
var _MaxDate = 
CALCULATE(
    MAX('dim_date'[Week Start]),
    FILTER(
        ALL('Dummy'),
        not ISBLANK('Dummy'[Pending])
    )
)
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
)

The table result I get is this:

My dummy data looks like that:

[Irwan]

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.

[some_analyst]

Thank you @Irwan , this is nearly it. Do you know if there is a way that user can manipulate the field input - e.g. change it to another number just on the page? and the numbers of forecast will update by deducting that number for the following weeks?