cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataUser
Helper I
Helper I

Scenario building using dates & What-if Parameters

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. 

 

DataUser_0-1616186002189.png

 

9 REPLIES 9
lbendlin
Super User III
Super User III

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

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
Helper I
Helper I

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
Community Support
Community Support

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))

 

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

v-robertq-msft_0-1616383231915.png

 

  1. 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]))

 

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

v-robertq-msft_1-1616383231924.png

 

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.

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]))

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?

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

lbendlin
Super User III
Super User III

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

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors