DATEDIFF across twotables

Question

Hello,&nbsp;Can anyone help me with what I hope is a straight forward question please?&nbsp;I have 3 related tables:&nbsp;Items; unique records desribing the item, related to both Transactions and OutcomesTransactions; refers to items but can have many transactions for the same item across different dates,related solely to ItemsOutcomes; refers to items but can have many outcomes for the same item across different dates, related solely to Items&nbsp;The item table contains the item name, item category and an item creation date. The transaction table also holds the creation date as well as the transaction date. I use this to show how many days between creation and the transaction for efficiency reporting. I now need to also report on how many days between a transaction and the outcome for each item. Unfortunately the outcome table does not have the creation date field (and I don't control the source system) and is not directly related to the transactions table. Is there a way I can reference the item name to achieve this?&nbsp;I have tried a number of different DATEDIFF statements based on examples shown here by the community (both as measures and calculated columns), but because of the many aspects of both the transactions and outcomes tables the function asks me to aggregate using MIN, MAX or similar, none of which actually provide the "expected" answer.&nbsp;What have I overlooked?&nbsp;Many thanks in advance.Regards,Chris&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-danhe-msft · Answer

Hi&nbsp;chris_staines,From your description, do you mean to calculate the days between the date in&nbsp;'TRANSACTIONS' table and date in 'OUTCOMES' table? If so, it seems that the two tables have a many to many relationship, one item have multiple date and which date should be calcuated? If I&nbsp;misunderstand you, could you please offer me more information about your data stucture and your desired result?&nbsp;Regards,Daniel He&nbsp;

chris_staines · Answer

Hello Daniel,&nbsp;Ideally, yes that is what I would like to be able to do. In the case of the multiple dates, if it is possible to select the first outcome *after* the transaction date that would be great.&nbsp;I managed to get a result using MIN or MAX, which for&nbsp;some cases should be fine, but where there are multiple entries for the same item it can obviously give the wrong result...&nbsp;Please follow this link for my example pbix file:&nbsp;https://drive.google.com/open?id=1wYY-um8g7rCLBGFm8M2Y02JaWNXEptjY&nbsp;Many thanks,Chris&nbsp;&nbsp;

v-danhe-msft · Answer

Hi&nbsp;chris_staines,From your description, I could not undestand the sntence "if it is possible to select the first outcome *after* the transaction date", if I selecet the first data like the picture below (2018/1/25), the related tranaction data should be with the Item name? What about the second date(the second 2018/1/25) shoud be related to which dat? Could you please post your desired result if possible?&nbsp;Regards,Daniel He

chris_staines · Answer

Hello Daniel,&nbsp;&nbsp;I realise my description wasn't ideal, please let me try again.&nbsp;What I would like to do is report against all items in the transactions table, and where an outcome exists for that item.&nbsp;For example if Item 1 has had 3 transactions, but only 2 outcomes from those transactions I would expect to see something like this:&nbsp;Item&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; Creation Date |&nbsp; Transaction Type |Transaction Date |&nbsp; Outcome Date |&nbsp; OutcomeI00000001 |&nbsp; 01/01/2018&nbsp; &nbsp; |&nbsp; &nbsp;Trans A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 16/01/2018&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 25/01/2018&nbsp; &nbsp;|&nbsp; POSI00000001 |&nbsp; 01/01/2018&nbsp; &nbsp; |&nbsp; &nbsp;Trans B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 22/03/2018&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 02/01/2018&nbsp; &nbsp;|&nbsp; NEGI00000001 |&nbsp; 01/01/2018&nbsp; &nbsp; |&nbsp; &nbsp;Trans C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 01/06/2018&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; 25/06/2018&nbsp; &nbsp;|&nbsp;Now the problem is, there is no obvious way to relate the transaction date to the outcome date as there is no transaction ID recorded in the outcome table, so I hoped there may be a way to summarize the data for an item so the first outcome date that occurs AFTER the current transaction date is displayed.&nbsp;Does that make any more sense?&nbsp;Thanks again for looking at my question.Regards,Chris

Forum Discussion

DATEDIFF across twotables

4 Replies

Related content

DATEDIFF DAX

DATEDIFF Working Days

DateDiff Incorrect output

DATEDIFF

DATEDIFF excluding weekends