Forum Discussion

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

Community Champion

3 years ago

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

Helper 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
Community 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
  Helper 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.
  - tamerj1
    Community Champion
    3 years ago
    Amitkr174
    
    Calculated columns do not interact with the filter context. This has to be a measure
    
    =
    VAR T =
    ALLSELECTED ( 'Table' )
    RETURN
    MAXX (
    'Table',
    IF (
    'Table'[CreatedDate] > 'Table'[Case_Comp_date]
    && CALCULATE (
    MIN ( 'Table'[CreatedDate] ),
    T,
    VALUES ( 'Table'[Opp_DealR] ),
    VALUES ( 'Table'[CaseNumber] )
    ) = 'Table'[CreatedDate],
    INT ( 'Table'[CreatedDate] - 'Table'[Case_Comp_date] )
    )
    )