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

     

     

     

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

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.