Forum Discussion

Amitkr174's avatar
Amitkr174
Icon for Helper III rankHelper III
3 years ago

Date DAX Calculation

Hi, tamerj1  johnt75  parry2k Jihwan_Kim ThxAlot VahidDM      I am trying to create a DAX for the below case:- IF ( (I:O createddate) > Case Completion Date), then     (I:O createddate) - Case ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Amitkr174 

[Comp_Created_datediff] is most likely a calculated column. Please double check. The DAX that you have provided would result in an error if used in a measure. 
It is important to have accurate information about the problem in order to be able to solve it. 

Amitkr174's avatar
Amitkr174
Icon for Helper III rankHelper III
3 years ago

yes, you are correct, it is a calculated column.

Condition is - if Createddate>CaseCompdate,then Createddate-CaseCompdate,else NULL.

It should stop once Createddate>CaseCompdate is true, rest of the lines in the column should be blank. Output sample is attached.

 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Amitkr174 

    Please try

    =
    IF (
    'Table'[CreatedDate] > 'Table'[Case_Comp_date]
    && CALCULATE (
    MIN ( 'Table'[CreatedDate] ),
    ALLEXCEPT ( 'Table', 'Table'[Opp_DealR], 'Table'[CaseNumber] )
    ) = 'Table'[CreatedDate],
    'Table'[CreatedDate] - 'Table'[Case_Comp_date]
    )

    • Amitkr174's avatar
      Amitkr174
      Icon for Helper III rankHelper III
      3 years ago

      Below is the result I am getting- Measure 4. I am not able to change the format as well. Please advise.

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Amitkr174 

        =
        IF (
        'Table'[CreatedDate] > 'Table'[Case_Comp_date]
        && CALCULATE (
        MIN ( 'Table'[CreatedDate] ),
        ALLEXCEPT ( 'Table', 'Table'[Opp_DealR], 'Table'[CaseNumber] )
        ) = 'Table'[CreatedDate],
        INT ('Table'[CreatedDate] - 'Table'[Case_Comp_date] )
        )