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!
Solved! Go to Solution.
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 )
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 )
Unlike many accepted solutions in the whole forum.
This actually works.
Thanks, Zubair for sharing your knowledge.
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:
Hey,
Is there a way to get Date value instead of Date difference as output?
@Zubair_Muhammad wrote:
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!
see attached file as well
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
User | Count |
---|---|
200 | |
71 | |
67 | |
55 | |
52 |
User | Count |
---|---|
253 | |
224 | |
102 | |
81 | |
71 |