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

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

        Thanks tamerj1  - it is working fine for one Deal Number but when I am selecting another one it is getting blank.

        When I select DR3630255, it is not showing in the first table though it has a Createddate > Case Comp date i.e. 18th May. Please advise.