Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mblydt-hansen
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 it currently looks like.This is what I want it to look like.This is what I want it to look like.
                                                         

 

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

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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

Please try my custom visuals

View solution in original post

13 REPLIES 13
Smshinde21
New Member

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? 🙏

akhatri
Helper I
Helper I

@Zubair_Muhammad is there any way of adding another perameter in your formula to only count the daydiff between the Closed Won opportunities

Zubair_Muhammad
Community Champion
Community Champion

@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

Please try my custom visuals
Anonymous
Not applicable

Nearly 5 years later this answer got me out of a jam, thanks so much!

Hello Zubair,

 

I have similar requirement just that in first column i have user name instead of user id but the solution is not working for me. 7th and 8th line of formula below is not accepting parameter after EARLIER. Please advise

 

 Table1[Patient ID] = EARLIER ( Table1[Patient ID] )
                && Table1[Date of Visit] < EARLIER ( Table1[Date of Visit] )

 

 Regards,

Kriti

Anonymous
Not applicable

Hi there,

 

Do you have any advise for my issue? 

 

This code works to get the same output for my dataset, however I need the 'number of days since last visit' to become 'days until next visit'. Essentially meaning all values move up by 1 row. So in the example above the result '5' moves from row 3 to row 2. This is because I have a further column to consider of 'treatment type' per patient ID and need to be able to graphically present the mean time until next visit per treatment type, so I need the 'days until next visit' result in the same row as 'treatment type.' 

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 )
 
However, it does not work. Could you please help me?
 
Thank you!
Anonymous
Not applicable

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 )

 

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!

@mblydt-hansen

 

see attached file as well

 

dd.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.