Forum Discussion
DAX for datediff of values different rows
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.
Community SupportHi 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.
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
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.
This resolved it, thank you very much!