Forum Discussion
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!
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 )
13 Replies
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 )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
I have the same problem how you solve it?
This is great - I'm getting exactly what I had hoped for. Thank you so much for your help!
Zubair_Muhammad is there any way of adding another perameter in your formula to only count the daydiff between the Closed Won opportunities
Hi All,
I'm looking for a support.
I want to calculate number of days between first and last date in the same column where I'm having multiple same ID entries with multiple dates
Can I get support with the DAX function logic? 🙏
