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.

     

     

     

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.

 

 

 

  • Antonio195754's avatar
    Antonio195754
    Icon for Helper IV rankHelper IV
    3 years ago

    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.

     

     

    • Antonio195754's avatar
      Antonio195754
      Icon for Helper IV rankHelper IV
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.