Add Days to Date Field on Status Change


#1

Hello all,

I have a sales pipeline report that I am building at this time. Trying to figure out how I can create a custom column/conditional column for [Estimated Closing Date] that will update when the [Deal Status] is changed to progress through the pipeline.

I want to use that field to slice by the 120/90/60/30 days till close.

For simplicity let’s say we have 5 stages as follows:

  1. Lead (120 days to close)
  2. Proposal (90 days to close)
  3. Listing (60 days to close)
  4. In Contract (30 days to close)
  5. Closed

I’d like to add the number of days per [Deal Status] to the [Estimated Closing Date] field when the [Deal Status] changes. I’m housing this data in SharePoint list, so I do have access to the [Modified] date field.

I can’t quite seem to put it together though at this time.

So I guess I am looking for a DAX:

if Deal Status is Lead, then add 120 days, if Deal Status Proposal, then add 90, if Deal Status Listing, then add 60, if Deal Status in Contract, then add 30 to Estimated Closing Date

As usual, not the easiest way to explain via typing. If any more info is needed I will gladly supply it.

Thank you in advance.


#2

Going to need some help here with the model and data tables. Too difficult to imagine everything. Thanks

The more images the better around what your working with currently. Thanks


#3

Thanks Sam. I’ll try to get some screenshots that make sense in here soon.


#4

Decided to take another route on this.