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

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