What If analysis to adjust date values?

[erhodes]

I'm wanting to use the new What If peramter functionality in a report to allow users to adjust dates. The use case is for a sales pipeline. We currently have defined est. close dates from CRM. The What If perameter would allow a user to enter a whole number (ideally -91 to +366) so they can see what the pipeline would look like if the estimated close dates moved up or back. I set up the What If as a whole number with a min of -91 and max of +366 w/ no issues. The block I hit was the next step. I tried to set up a claculated column to add the measure created from the creation of the what if perameter to the estimated close date but that isn't returing a value. Anyone tackled someting like this before?

[malagari]

You don't want to use a calculated column for the last part.  Calculated columns are calculated when the model is refreshed, not when a slicer changes value.  The problem here is you're allowing users to change the What-If slicer, and that won't update the calculated column each time.

If you change your "New Est. Close Date" to a measure, which does a DATEADD([Est. Close Date], [What-If-Value], DAY), it will react properly to your dynamic What-If slicer.

[erhodes]

Thanks for the suggestion. I tried that and got the followng error: "A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.

[malagari]

Ah, right, I always forget that DATEADD takes a column of unique dates.  

Since you're working with days on the What-If slicer, you should be able to just do = [Est. Date] + [What-If Slicer Value].  This might throw another error saying that "more than one value was returned for [Est. Date]", in which case you'll have to consider how this measure is being used.  

If it's being used always in unison with Opportunity/Sales name, then you could do MAX([Est. Date]).  This DAX pattern gets a little more involved that I can describe here.

[erhodes]

Using measure MAX(Est. Close Date) + [What If Value] produces a measue value that is correct when viewed in a grid table itemized by opportunity name.  The problem is that since this isn't a calculated column, I can't create an inactive relationship w/ a date table and then calculate/show the est. contract value by date based on the What If scenario.

[v-jiascu-msft]

Hi @erhodes,

Can you post a dummy sample here in text mode? Maybe we can try this.

a =
CALCULATE (
    SUM ( [contract value] ),
    FILTER ( datetable, datetable <= MAX ( [est. close date] ) + [what if value] )
)

Best Regards!

Dale

[mcharing]

Hi,

In your measure you use a FILTER which implies row context; in Filter you are relating to a datetable (I assume their is one) and compare this to the "est. close date" from the fact table. How does this work? Isn't it needed to use RELATED since where in row context their?

[erhodes]

Thanks for the reply. I tried that measure and it is only showing the contract value (specifically I'm using weighted contract value) for 2017. Below is a sample data set. 

Measure: Weighted Est. Contract Value = CALCULATE(
SUM(
OpportunitySet[Weighted Est. Contract Value]),
OpportunitySet[Status]="Open",
USERELATIONSHIP(OpportunitySet[Est. Close Date],DateTbl[Date]))

Essentially, I want to show the current pipeline by total Weighted Est. Contract Value by date. Then the What If Parameter will allow users to say "What if the Est. Close Date moves X # of days (ideally fwd or backwar), how does that change the total Weighted Est. Contract Value by Date?

 

[v-jiascu-msft]

Hi @erhodes,

Since there is a move in dates, which date is the baseline? For example, if today (2017-09-06) is the baseline and move +30 days, the result would be 100 (Deal 1) + 200 (Deal 2) = 300. Is this the way to handle?

Best Regards!

Dale

[erhodes]

The baseline is the est. close date value. So if the what if peramter slider was set at 30, it would move all est. close dates +30. Ultimately, I'm lookign to show a chart similar to below. When est. close dates shift, the contract values estimated to close may also shift to different months, qtrs, years. 

[mcharing]

Hi Erhodes,

Has this challenge been solved already? I am facing a similar issue and I am not able to get it to work. and I do not even have to show the original scenario; just move the value when the scenario changes.

My main problem seems to be that the date which I want to influence via a "What If" slicer is also the key to my date key.