DATEDIFF across twotables

[chris_staines]

Hello,

Can anyone help me with what I hope is a straight forward question please?

I have 3 related tables: 

Items; unique records desribing the item, related to both Transactions and Outcomes

Transactions; refers to items but can have many transactions for the same item across different dates,related solely to Items

Outcomes; refers to items but can have many outcomes for the same item across different dates, related solely to Items

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?

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.

What have I overlooked?

Many thanks in advance.
Regards,

Chris

[v-danhe-msft]

Hi @chris_staines,

From your description, do you mean to calculate the days between the date in '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 misunderstand you, could you please offer me more information about your data stucture and your desired result?

Regards,

Daniel He 

[chris_staines]

Hello Daniel,

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.

I managed to get a result using MIN or MAX, which for some cases should be fine, but where there are multiple entries for the same item it can obviously give the wrong result...

Please follow this link for my example pbix file: https://drive.google.com/open?id=1wYY-um8g7rCLBGFm8M2Y02JaWNXEptjY

Many thanks,

Chris

[v-danhe-msft]

Hi @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?

Regards,

Daniel He

[chris_staines]

Hello Daniel,

I realise my description wasn't ideal, please let me try again.

What I would like to do is report against all items in the transactions table, and where an outcome exists for that item.

For example if Item 1 has had 3 transactions, but only 2 outcomes from those transactions I would expect to see something like this:

Item          |  Creation Date |  Transaction Type |Transaction Date |  Outcome Date |  Outcome
I00000001 |  01/01/2018    |   Trans A               |  16/01/2018       |  25/01/2018   |  POS
I00000001 |  01/01/2018    |   Trans B               |  22/03/2018       |  02/01/2018   |  NEG
I00000001 |  01/01/2018    |   Trans C               |  01/06/2018       |  25/06/2018   |

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.

Does that make any more sense?

Thanks again for looking at my question.

Regards,
Chris