Forum Discussion

Antonio195754's avatar
Antonio195754
Icon for Helper IV rankHelper 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 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)
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.

     

     

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Antonio195754
      Icon for Helper IV rankHelper IV
      3 years ago

      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's avatar
        Antonio195754
        Icon for Helper IV rankHelper IV
        3 years ago

        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...