topic Re: Date DAX Calculation in DAX Commands and Tips

Date DAX Calculation

Amitkr174 — Mon, 26 Jun 2023 15:47:36 GMT

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.

Re: Date DAX Calculation

Greg_Deckler — Wed, 21 Jun 2023 16:07:22 GMT

Amitkr174 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Date DAX Calculation

tamerj1 — Wed, 21 Jun 2023 16:18:03 GMT

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?

Re: Date DAX Calculation

Amitkr174 — Thu, 22 Jun 2023 11:46:36 GMT

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?

Re: Date DAX Calculation

tamerj1 — Thu, 22 Jun 2023 13:39:45 GMT

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.

Re: Date DAX Calculation

Amitkr174 — Thu, 22 Jun 2023 14:22:55 GMT

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.

Re: Date DAX Calculation

Amitkr174 — Thu, 22 Jun 2023 14:27:25 GMT

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.

Re: Date DAX Calculation

tamerj1 — Thu, 22 Jun 2023 15:14:42 GMT

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

Re: Date DAX Calculation

Amitkr174 — Thu, 22 Jun 2023 15:51:54 GMT

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

Re: Date DAX Calculation

tamerj1 — Thu, 22 Jun 2023 16:17:46 GMT

Amitkr174

Re: Date DAX Calculation

Amitkr174 — Fri, 23 Jun 2023 12:35:36 GMT

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.

Re: Date DAX Calculation

tamerj1 — Fri, 23 Jun 2023 14:24:32 GMT

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

Re: Date DAX Calculation

Amitkr174 — Fri, 23 Jun 2023 14:41:58 GMT

Still same issue is there.

Re: Date DAX Calculation

Amitkr174 — Mon, 26 Jun 2023 14:37:50 GMT

tamerj1 - Any update on this?

Re: Date DAX Calculation

Amitkr174 — Mon, 26 Jun 2023 15:48:10 GMT

johnt75 parry2k Jihwan_Kim ThxAlot VahidDM

can someone please help on this?

Re: Date DAX Calculation

tamerj1 — Mon, 26 Jun 2023 16:02:21 GMT

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

Re: Date DAX Calculation

Amitkr174 — Tue, 27 Jun 2023 05:20:53 GMT

tamerj1 Now it is showing blank. Please advise.

Re: Date DAX Calculation

tamerj1 — Tue, 27 Jun 2023 05:43:55 GMT

Amitkr174

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

Re: Date DAX Calculation

Amitkr174 — Tue, 27 Jun 2023 08:02:01 GMT

tamerj1 Sure, let me know when.

Re: Date DAX Calculation

Amitkr174 — Wed, 28 Jun 2023 10:39:54 GMT

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