DAX for datediff of values different rows

[patrickoleary85]

Hi,

I am trying to use datediff in a column to gather the number of days between two events. I'm unable to produce the data. I think it something small I am missing, but I cannot figure it out. 

Here is a visual of my data. On the lefthand side, it is displaying each user, then a created receipts date and a created results date. 

For the item below, the Results date and Receipts date are two different events for the same user. I am trying to calculate the amount of time from Receipt to Results. I can provide a bit more information but hopefully this is enough to gather needed support. Thank you in advance

[v-yanimei-msft]

Hi all , 

Thanks for the reply from @Joe_Barry ,please allow me to provide anther insight:

Based on the information you show, I will give you the following steps to solve the problem.

1.The test table Table.

^{2.New Measure. As an example, if you want the date difference between the Receipts date and Results date when User is AA, you can use the following DAX.} LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

AA's DateDiff = 
//Find the created date of document that User is AA and Document type is Receipt.
VAR StartDate = LOOKUPVALUE('Table'[Created],'Table'[Document Type],"Receipt",'Table'[User],"AA")
//Find the created date of document that User is AA and Document type is Results.
VAR EndDate = LOOKUPVALUE('Table'[Created],'Table'[Document Type],"Results",'Table'[User],"AA")
RETURN DATEDIFF(StartDate,EndDate,DAY)

3.The outcome is in the following picture. AA's DateDiff is added to Card’s Fields.

If you still have trouble in it, you may need to answer some questions.

1. In what form do you want to display the results you want? Want to select users with a slicer? Are the results displayed on cards or in a table? If so, what are the display requirements?

2.Do you have only two document types (Receipt and Results) for each user? If there are other types, how do you want to do it?

^{Best Regards,
Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[patrickoleary85]

Hi, I believe this would work but I was blocked while creating the VAR StartDate. The issue is that I need to gather data for all Users, and the logic you gave only allows me to look at a single user. Is there a modification to this logic that would allow me to look at all users?

[v-yanimei-msft · Accepted solution]

Hi @patrickoleary85 , 

Based on your response, I understand that you want to see the date difference for all users. So, I made a modification to the test table Table as shown in the image. New column Diff between Receipt and Results and input:

Diff between Receipt and Results = 
   VAR A = MAXX(FILTER('Table',EARLIER('Table'[User])  = 'Table'[User]),'Table'[Created].[Day])
   VAR B = MINX(FILTER('Table',EARLIER('Table'[User])  = 'Table'[User]),'Table'[Created].[Day])
   RETURN
   IF('Table'[Document Type]="Receipt",B-A,A-B)

You can modify the DAX code according to your needs. Hope it works for you!

Best Regards,

Caroline Mei

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[patrickoleary85]

This resolved it, thank you very much!

[Joe_Barry]

Hi @patrickoleary85 

Do the Results date and Receipts date appear in the same line in the table? Create a calculated columns and add the measure below, relacing the Table with your table name. I hope this helps!

Diff = DATEDIFF(Table[Results date], Table[Receipts date], DAY)

If you found my answer helpful and it solved your issue, please accept as solution

[patrickoleary85]

Hi and thank you, Joe! This wasn't successful for me. The items are not on the same line/row. So the two types of Document Type look at the same Created column