Forum Discussion
Helper IScenario 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.
9 Replies
- v-robertq-msft
Community Support
Hi, DataUser
According to your description, I can understand clearly what you want to get, you can try my steps:
- Create a calendar table based on your data range like this:
Date = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))- Create a What-if parameter and add a slicer to the page like this:
- 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]))- 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.
- lbendlin
Super User
You don't even need the SELECTCOLUMNS part since the calendar returns a single column table.
DataUserSo - 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?
- 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]))
- 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?
- 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.
- 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?
