Forum Discussion

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago
Solved

Date difference in same column for different stage in text format

Could someone please help with this DAX?    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 st...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Antonio195754 ,

     

    I think there should be multiple projects which should be keynames and there are multiple stages in them.

    Please try this code to create a calculated column.

    DateDiff = 
VAR _PreviousDate =
    CALCULATE (
        MAX ( 'B Opportunity with History'[CREATEDATE] ),
        FILTER (
            ALLEXCEPT ( 'B Opportunity with History', 'B Opportunity with History'[Project] ),
            'B Opportunity with History'[CREATEDATE] < EARLIER ( 'B Opportunity with History'[CREATEDATE] )
        )
    )
VAR _DATEDIFF =
    DATEDIFF (
        IF ( _PreviousDate = BLANK (), 'B Opportunity with History'[CREATEDATE], _PreviousDate ),
        'B Opportunity with History'[CREATEDATE],
        DAY
    )
RETURN
    _DATEDIFF

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago

Thank you Anonymous I was trying to do a calc column and it did work after i cleaned up some  memory.  However i was just notified that the data points and columns have changed in the data source.  It looks to be laid out in an easier format to work with which is great, but i'll reach out if i have any questions.  Thank you so much

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago

Anonymous amitchandak  With the change on the table provided by client, I think it may be easier to make work with the new layout.  Below is the link to CSV file of a sample.  

 

Please note that each Opportunity ID is the sale ID.  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 Closed Won, and Working to 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.  

 

Any help would be much appreciated!

 

https://drive.google.com/file/d/17ibHR1NsayUsqBK9iXEKvVq5ch0j0NLe/view?usp=sharing