Date Diff on Different tables with multiple date values

[RoyalJ]

Hi,

I have seen others ask something similar to this but not the exact issue I am facing. I have a large data model with different information about sales to clients. In one table I have he information about the party (party ID, Join_Date etc) and in another table I have Trade info (Party ID,Trade ID, Trade Date). I want to create a Date Diff column that measures the time between their first Join and their first Trade (people can have multiple accounts multiple (join_dates and trade dates)).

The issue also seems to be that they can have multiple first join and trade dates if they opened the accounts and made the trades at once. I will try to show this below also.

Party Table

Party ID	Account ID	Join Date
1	1A	01/01/2024
1	1B	01/01/2024
1	1C	07/01/2024
1	1D	11/01/2024
2	2A	02/01/2024
2	2B	02/01/2024
3	3A	07/01/2024

Trade Table

Party ID	Account ID	Trade Date	Trade ID
1	1A	04/01/2024	sdadasd
1	1B	09/01/2024	feefeww
1	1C	11/01/2024	fw43gg4
1	1D	11/01/2024	g4343ttrw
2	2A	08/01/2024	43g34g4
2	2B	08/01/2024	g5jh67jj
3	3A	25/01/2024	34g35ztg

I have tried performing the measure 

Diff = DATEDIFF(MIN(PARTY[JOIN_DATE]), TRADE[TRADE_DATE], DAY)

However I find this only works contextually and doesnt work as a filter within another measure nor as a slicer (only as a filter on a table visual)

Thanks in advance

[lbendlin · Accepted solution]

Your Account ID seems to be a primary key.  Create an Account dimension that lists the Account IDs and then control both of your fact tables from that dimension.  Then the measures write themselves.

[Anonymous · Accepted solution]

Hi @RoyalJ 

Thanks to @lbendlin  for your prompt reply. Here are my additions:

Measure does not work as a slicer, but it can be put into Filters on this visual, I'm just not sure what kind of filtering you need to do, we are better able to help you if you can provide more information.

Here is the testing I've done so far.

Create another Measure to filter out IDs with datediff > 5 days

>5 = CALCULATE(MAX([Trade ID]), FILTER(Trade, [Diff] > 5))

Output:

Or put the Diff into a visual level filter for filtering.

If I've misunderstood you, please provide the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

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

[lbendlin · Accepted solution]

Your Account ID seems to be a primary key.  Create an Account dimension that lists the Account IDs and then control both of your fact tables from that dimension.  Then the measures write themselves.