Scenario building using dates & What-if Parameters

[DataUser]

Hullo  - I know I need to use some combo of time/date slicers and a what-if parameter for this, but I'm struggling to put them together correctly. 

Objective is for a user to be able to pick a start date & end date and an amount, then, a line graph updates with the what-if scenario numbers. 

[lbendlin]

You can't use calculated columns for modeling. Has to be measures.

[DataUser]

Yeah, makes sense. I thought I'd try calc column since I read somewhere that because its a sharepoint list, you just make the measure a column. 

Still - the solution provided won't work since my data is coming from Sharepoint Online and the MAX expression doesn't function the same way. Any advice? 

[DataUser]

And - Lastly, just used my working "Test" of the provided solution and add a data source from Sharepoint and the solution provided above does NOT work with a sharepoint lists. 

Any suggestion for a solution that will work when using Sharepoint Online Lists?

[v-robertq-msft]

Hi, @DataUser 

According to your description, I can understand clearly what you want to get, you can try my steps:

1. Create a calendar table based on your data range like this:

Date = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))

2. Create a What-if parameter and add a slicer to the page like this:

3. Create a measure in the ‘Table’ like this:

What-if Amount =

IF(

    MAX('Table'[Date]) in ALLSELECTED('Date'),

    MAX('Table'[Amount])+[Parameter Value],

MAX('Table'[Amount]))

4. Then create a data range slicer and a table chart and place them like this:

And you can get what you want.

You can download my test pbix file here

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataUser]

When I try to switch it over to a calculated column, it's still not functioning correctly. My formula looks like this but all that it returns is the maximum value in the AmountSharepointList[AMOUNT] column, so I assume it's reading the condition as "False" for some reason.

WhatifColumn = IF(

MAX(AmountSharepointList[Date])in ALLSELECTED('Date'),

MAX(AmountSharepointList[AMOUNT])+[What-If Capacity Value],

MAX(AmountSharepointList[AMOUNT]))

[DataUser]

So - I built my own test version based on what you wrote and it definitely works. Unfortunately, it isn't working when I try to apply it within my actual report and I am trying to troubleshoot why. Would this type of formula be impacted if the source data isn't from a table but is from a linked Sharepoint List? Is there another method I could try?

[lbendlin]

You don't even need the SELECTCOLUMNS part since the calendar returns a single column table.

[lbendlin]

Do you want to also show the original values for the other dates?  And only lift the selected values by the parameter amount?

[DataUser]

Yes - Ultimately, I would like to add this What if Capacity to a line graph where I have multiple other amounts graphed over the course of the year. I was just going to use the new measure as another value in the line graph and keep the original set of values as another.