Date difference in same column for different stage in text format

[Antonio195754]

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 stage "Assigned", "Working", "Rejected", "Won". And the CREATEDATE column are the dates from one stage to another.

What i'm trying to do is provide how many days average it takes to move 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 rejected, and working to won.

This is what i tried, but it won't work b/c my stages are in text and while i tried duplicating the column and assigned values to the stages in the order they should come in (1-4), it still didn't take.  It's saying a table with multiple values was supplied where a single value was expected. Don't think that is the right approach.  And i really would prefer to use the existing stage names in text format if it's possible.

DateDiff =

VAR _PrevDate = LOOKUPVALUE('B Opportunity with History'[CREATEDATE],'B Opportunity with History'[STAGE],'B Opportunity with History'[STAGE]-1)

VAR _PrevDateClean = IF(_PrevDate=BLANK(),'B Opportunity with History'[CREATEDATE],_PrevDate)

RETURN DATEDIFF(_PrevDateClean,'B Opportunity with History'[CREATEDATE],DAY)

[amitchandak]

@Antonio195754 , try with earlier

DateDiff =
var _PrevDate = maxx(filter('B Opportunity with History', 'B Opportunity with History'[STAGE] = earlier('B Opportunity with History'[STAGE]) -1),'B Opportunity with History'[CREATEDATE])
VAR _PrevDateClean = IF(isblank(_PrevDate),'B Opportunity with History'[CREATEDATE],_PrevDate)
RETURN DATEDIFF(_PrevDateClean,'B Opportunity with History'[CREATEDATE],DAY)

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[Antonio195754]

Thank you @amitchandak !

I got it to accept that DAX you provided, but it came back with negative numbers, some in 100's where it really should only be about 30 or so days on average from one stage to another.  Is it possible rather than using the stage names 1-4, to use the text names of the stages, e.g. "Assigned", "Working", "Rejected", "Won", where it is days difference from assigned to working, from working to rejected, and working to won?

[Antonio195754]

These are the columns i'm using @amitchandak, for the stages.  Prefer to use the text format of the stage names but if numeric works better, all good.  Ultimately they will go in scorecards where each card would be, "Assigned to Working", "Working to Won", "Working to Rejected". I feel like i would almost have to make a calculated column for each visual...

[Anonymous · Accepted solution]

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]

Thank you @Anonymous and to your point, it does make sense that we need to include what i'll call the "sale ID" in the formula.  When I plug that into PBI i get this error when trying to replace the measures i need.  In the screenshot are the actual measures i'm using.  Opportunity_Type_C being the Sale ID.

[Anonymous]

Hi @Antonio195754 ,

According to your screenshot, I think you are creating a measure instead of a measure. If you want to create a measure, please try code as below. We can use column name directly in calculated column. However, the measure will return aggregation. So we need to use function like max/min/sum to determind the value.

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]
                < MAX ( 'B Opportunity with History'[CREATEDATE] )
        )
    )
VAR _DATEDIFF =
    DATEDIFF (
        IF (
            _PreviousDate = BLANK (),
            MAX ( 'B Opportunity with History'[CREATEDATE] ),
            _PreviousDate
        ),
        MAX ( 'B Opportunity with History'[CREATEDATE] ),
        DAY
    )
RETURN
    _DATEDIFF

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]

I figured out why it wasn't taking, but now it's saying i'm out of memory.  Let me work on cleaning up these tables i no longer am using and see if i can get your recommendation to work for me.  Thank you!

[Antonio195754]

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]

@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