cancel
Showing results for
Did you mean:
Frequent Visitor

Date difference between values in same column

Hi everyone!

I'm still learning DAX and I was wondering if the collective minds of this forum could help me out with coding a custom column that I've been having difficulty with. I feel like my issue is relatively simple, but I haven't come across any posts on this forum which relate.

I'm trying to code a custom column which generates the number of days between two dates. The two dates are located in the same column, and I want to find the number of days between two chronologically adjacent dates when there are multiple date values - eg. the number of days between date 1 and date 2, date 2 and date 3, date 3 and date 4, etc. (when date 1 is the earliest date and date 4 is the latest date). This needs to be done for each unique patient ID value.

I've included a mock-up of what I'm trying to accomplish below.

This is what it currently looks like.This is what I want it to look like.

I'd appreciate any help I can get. Thank you!

1 ACCEPTED SOLUTION
Super User

@mblydt-hansen

You could use this calculated column

```Column =
VAR temp =
TOPN (
1,
FILTER (
Table1,
Table1[Patient ID] = EARLIER ( Table1[Patient ID] )
&& Table1[Date of Visit] < EARLIER ( Table1[Date of Visit] )
),
[Date of Visit], DESC
)
RETURN
DATEDIFF ( MINX ( temp, [Date of Visit] ), Table1[Date of Visit], DAY )```

Regards
Zubair

8 REPLIES 8
Super User

@mblydt-hansen

You could use this calculated column

```Column =
VAR temp =
TOPN (
1,
FILTER (
Table1,
Table1[Patient ID] = EARLIER ( Table1[Patient ID] )
&& Table1[Date of Visit] < EARLIER ( Table1[Date of Visit] )
),
[Date of Visit], DESC
)
RETURN
DATEDIFF ( MINX ( temp, [Date of Visit] ), Table1[Date of Visit], DAY )```

Regards
Zubair

Frequent Visitor

Unlike many accepted solutions in the whole forum.

This actually works.

Thanks, Zubair for sharing your knowledge.

Anonymous
Not applicable

Hi,

I have a similar problem. I have 3 columns, one with different products IDs, one with different status and one with dates, which replicate lines adding one day until the product status changes. So I want to calculate the difference between the max date and min date for determined product ID and status.

I applied something similar:

Column =
VAR temp =
TOPN (
1,
FILTER (
Teste,
Teste[Work Item Id] = EARLIER ( Teste[Work Item Id] ) && Teste[State] = EARLIER ( Teste[State] )
&& Teste[Date] < EARLIER ( Teste[Date] )
),
[Date], DESC
)
RETURN
DATEDIFF ( MINX ( temp, [Date] ), Teste[Date], DAY )

Thank you!
Anonymous
Not applicable

Hey,
Is there a way to get Date value instead of Date difference as output?

@mblydt-hansen

You could use this calculated column

```Column =
VAR temp =
TOPN (
1,
FILTER (
Table1,
Table1[Patient ID] = EARLIER ( Table1[Patient ID] )
&& Table1[Date of Visit] < EARLIER ( Table1[Date of Visit] )
),
[Date of Visit], DESC
)
RETURN
DATEDIFF ( MINX ( temp, [Date of Visit] ), Table1[Date of Visit], DAY )```

Helper I

Hi,

I have followed your footsteps to recreate this DAX script and the following errors are being popped out: (in Red)

```Frequency =
VAR temp =
TOPN(1, FILTER(
&& Freq[new_Transaction_Date] < EARLIER( Freq[new_Transaction_Date])
),
Freq[new_Transaction_Date], DESC
)
return
DATEDIFF(MINX(temp, Freq[new_Transaction_Date]), Freq[new_Transaction_Date], DAY)```

Do you minf revieing my code?

Helper V

I have the same problem how you solve it?

Frequent Visitor

This is great - I'm getting exactly what I had hoped for. Thank you so much for your help!

Super User

@mblydt-hansen

see attached file as well

Regards
Zubair