Date difference between values in same column

[mblydt-hansen]

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!

[Zubair_Muhammad · Accepted solution]

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

[nrungta]

Unlike many accepted solutions in the whole forum.

This actually works. 

Thanks, Zubair for sharing your knowledge. 

[Anonymous]

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 )

However, it does not work. Could you please help me?

Thank you!

[Anonymous]

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

---

@Zubair_Muhammad wrote:

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

---

[sv11]

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, Freq[new_AdvisorCRD] = EARLIER(Freq[new_AdvisorCRD]) 
   && 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?

Thanks for your help

[MAAbdullah_47]

I have the same problem how you solve it?

[mblydt-hansen]

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

[Zubair_Muhammad]

@mblydt-hansen

see attached file as well