Date DAX Calculation

[Amitkr174]

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 Completion Date, 

     ELSE NULL)

I have created a DAX -  

IF([CreatedDate]>[Case_Comp_date],

           [CreatedDate]-[Case_Comp_date], BLANK())

Problem i am facing is when there are multiple dates with (i:o)Createddate>comp date for one DR ID. e.g.-For DR3622342, it should show blank from 2 row onwards.

 Please help on this.

[Amitkr174]

@johnt75  @parry2k @Jihwan_Kim @ThxAlot @VahidDM 

can someone please help on this?

[tamerj1]

Hi @Amitkr174 

I can tell that [CreatedDate] and [Case_Comp_date] are columns not measures and [Comp_Created_datediff] is a calculated column (summarized by sum) and not a measure as the total sums the individual values of the rows. Please confirm. I can also notice that both date columns are of text data type as they are alligned left. 
However, what is confusing me is how the subtraction is actually calculating correct difference. Do you have other date columns of date data type?

[Amitkr174]

Yes, columns  [CreatedDate] and [Case_Comp_date] are not measures. [Comp_Created_datediff] is a measure DAX is -

IF([CreatedDate]>[Case_Comp_date],

           [CreatedDate]-[Case_Comp_date], BLANK())

Could you please let me know how to check for a date column, if the following date is greater or lesser from the previous date?

[tamerj1]

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

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]

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

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

[tamerj1]

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

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]

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

[Amitkr174]

Still same issue is there.

[Amitkr174]

@tamerj1  - Any update on this?

[tamerj1]

@Amitkr174 
Apologies, your reply was overlooked somehow. Yes you are right there was a typo mistake.

Please try

=
VAR T =
    ALLSELECTED ( 'Table' )
RETURN
    MAXX (
        T,
        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] )
        )
    )

[Amitkr174]

 @tamerj1  Now it is showing blank. Please advise.

[tamerj1]

@Amitkr174 

We need to connect. I'll let you if today is possible. 

[Amitkr174]

I am not able to send you the message. Error msg is below. 

[Amitkr174]

Sample data @tamerj1 

Opp_DealR		CaseNumber	CreatedDate	Case_Comp_date	oldvalue	Newvalue
DR3622342		2197519	3/8/2023	2/26/2023	02 - Prospect	05 - Solution Definition and Validation
DR3622342		2197519	5/25/2023	2/26/2023	05 - Solution Definition and Validation	06 - Customer Commit
DR3622342		2197519	5/26/2023	2/26/2023	06 - Customer Commit	Closed - Booked
DR3622342		2197519	5/26/2023	2/26/2023	Closed - Booked	07 - Execute to Close
DR3622342		2197519	6/1/2023	2/26/2023	07 - Execute to Close	Closed - Booked
DR3630255		2248914	3/20/2023	5/1/2023	01 - Pre Call Plan	02 - Prospect
DR3630255		2248914	3/28/2023	5/1/2023	02 - Prospect	03 - Opportunity Qualification
DR3630255		2248914	4/18/2023	5/1/2023	03 - Opportunity Qualification	04 - Circle of Influence
DR3630255		2248914	5/18/2023	5/1/2023	04 - Circle of Influence	05 - Solution Definition and Validation

[tamerj1]

@Amitkr174 

Oh that error will go after a while. However, I won't be able to connect neither today nor tomorrow. I'll be available only on Friday. 

[Amitkr174]

@tamerj1  Sure, let me know when.

[Amitkr174]

DR#	ID	CaseNumber	CreatedDate	Case_Comp_date	Comp_Created_datediff	oldvalue	Newvalue
DR3622342	0065Y00001hNJCNQA4	2197519	2/15/2023	2/26/2023		02 - Prospect	05 - Solution Definition and Validation
DR3622342	0065Y00001hNJCNQA4	2197519	1/14/2023	2/26/2023		02 - Prospect	05 - Solution Definition and Validation
DR3622342	0065Y00001hNJCNQA4	2197519	3/8/2023	2/26/2023	10	02 - Prospect	05 - Solution Definition and Validation
DR3622342	0065Y00001hNJCNQA4	2197519	5/25/2023	2/26/2023		05 - Solution Definition and Validation	06 - Customer Commit
DR3622342	0065Y00001hNJCNQA4	2197519	5/26/2023	2/26/2023		06 - Customer Commit	Closed - Booked
DR3622342	0065Y00001hNJCNQA4	2197519	5/26/2023	2/26/2023		Closed - Booked	07 - Execute to Close
DR3622342	0065Y00001hNJCNQA4	2197519	6/1/2023	2/26/2023		07 - Execute to Close	Closed - Booked

yes, you are correct, it is a calculated measure. Attached is the sample date.

Condition is - if Createddate>Case Comp date, then Createddate-Case Comp date, else Null.

Rest of the rows should show blank values.