Days Difference using one date column based on another columns data points

[Antonio195754]

I'm wanting to find the date difference where i have a date column and another column that tells me a stage a given sale is in.  STAGE column includes stage "Assigned", "Working", "Won", "Rejected". And the CREATEDATE are the dates from one stage to another. The order i provided is the sequence the stages should be in.  So how long it takes from assigned to working, from working to won, and won to rejected. I've included a sample file of the data.

Please note that each Opportunity ID is the sale ID.  I think the Opportunity ID will need to be used in the DAX along with the OLDVALUE/NEWVALUE/CREATEDATE You will see duplicates of the ID because every time an ID changes from one "VALUE" (the Stagename) to another, a new row is created.  You'll see OLDVALUE and NEWVALUE.  OLDVALUE is just that, the old value before it was changed to the NEWVALUE.  CREATEDDATE is the Date/Time that VALUE/Stagename was changed.  There are blanks within the OLDVALUE and NEWVALUE that i think are key to get the date difference from one stage to another using the CREATEDDATE column.  The table is a logging of any change to a field. Therefore, if the old value is blank/null, then it never had a value and the create date on that row is the first time that field has been populated. Here's an example below link to file, looking at one specific Opportunity ID, where we can see all of the tracked changes for this specific Opportunity ID...and I'm highlighting in the results that there is a row logged for a change to the field 'Created'.

I really am only concerned when the OLDVALUE/NEWVALUE have gone from OLDVALUE Assigned, to NEWVALUE Working (Working is an aggregate of Working and Working -Contacted for both the OLDVALUE AND NEW VALUE columns), OLDVALUE Working to NEWVALUE Closed Won, and OLDVALUE Working to NEWVALUE Rejected (Rejected is an aggregate of Rejected, Recycled, and Retired for both the OLDVALUE AND NEWVALUE columns).  

This would have been much easier if there was a column that showed the OLDVALUE createddate as i could do a date difference of it to the NEWVALUE createddate, but not the case unfortunately.  

[Antonio195754 · Accepted solution]

Figured out how to tackle this one.  I just filtered the given outcome i needed to see and date a date difference of greater than 0 and put it in a scorecard.  Thanks everyone for the help!

[sturlaws]

Hi, @Antonio195754,

I had a look at the file, and there are a lot of combinations of NEWVALUE and OLDVALUE, and it is not obvious how these fit into assigned, working, closed won and rejected. Could you try to load your data into Power BI Desktop and do some cleaning with power query, and share the pbix-file?

Second, the screenshots you have provided are of low quality, and when zooming in on them, they are not readable.

Cheers,
Sturla

[Antonio195754]

Hey @sturlaws  my apologies for the blurry screenshots.  I've attached a link to a screenshot and have a sample PBI file to use.  I've filtered within the PBI file only the "stages" that are of value for this project.

The stage flows i'm looking for are when an oldvalue goes from Assigned to newvalue working, oldvalue working to newvalue won, and oldvalue working to newvalue returned (i've already aggrated the stages that need to be considered returned).  You'll notice that the oldvaluecreatedate will, for the most part, always be an earlier time stamp when compared to the newvaluecreatedate.  So i'm trying to get the days difference between those createdate columns, based on the oldvalue/newvalue stage outcomes i laid out above.

[sturlaws]

It seems like you have solved it yourself?

[Antonio195754]

I'm not confident what i tried is correct @sturlaws   The negative numbers i got is what questioned the logic i built out so far.