cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mblydt-hansen Frequent Visitor
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.

 

 

 

2018-07-04 (3).pngThis is what it currently looks like.2018-07-04 (2).pngThis is what I want it to look like.
                                                         

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Date difference between values in same column

@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 )
4 REPLIES 4
Super User
Super User

Re: Date difference between values in same column

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

Re: Date difference between values in same column

@mblydt-hansen

 

see attached file as well

 

dd.png

Highlighted
mblydt-hansen Frequent Visitor
Frequent Visitor

Re: Date difference between values in same column

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

sv11 Regular Visitor
Regular Visitor

Re: Date difference between values in same column

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