Forum Discussion
Advocate IIWhat If analysis to adjust date values?
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?
10 Replies
- malagari
Continued Contributor
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.
