Date diff between related tables

[Anonymous]

Hello, 

I have what is no doubt a simple query. I am trying to calulate the datediff between two dates in relared tables. The problem is complicated (maybe) by the use of a bridge table to link the tables and multiple rows to filter on one of the tables.

The structure is like this

Client Application
Client Record ID	Application date	Outcome Date
1	10/02/2020	05/05/2020
2	15/01/2020	04/05/2020
3	01/03/2020	08/05/2020
2	02/02/2020	03/05/2020
Bridge Table
Client Record ID (Unique)
1
2
3
Client Interactions
Client record ID	Date of interaction	Interaction type
1	01/03/2020	complaint
2	03/03/2020	complaint
2	08/02/2020	complaint
3	01/04/2020	compliment
3	01/05/2020	complaint

What i would like to calulate is the length of time from the clients first complaint to their eventual application outcome. 

I was trying to do this by bringing in the first interacion date as a column on the main application table and then doing datediff to calculate the difference between the first interaction and the outcome. The problem is however, that i  need to bring the first complaint. So i tried to do this with the following formula

First complaint = CALCULATE(min('Client interaction - Rows'[Date of session / Activity].[Date]),filter('Client interaction - Rows','Client interaction - Rows'[Type]="complaint")

but this just provides the first complain for any client against all rows.  Any tips on this? 

Thanks a lot 

DD

[v-juanli-msft]

Hi @Anonymous 

Create a column

first date =
CALCULATE (
    MIN ( 'Client Interactions'[Date of interaction] ),
    FILTER (
        ALLEXCEPT (
            'Client Interactions',
            'Client Interactions'[Client record ID]
        ),
        'Client Interactions'[Date of interaction] >= 'Client Application'[Application date]
            && 'Client Interactions'[Date of interaction] <= 'Client Application'[Outcome Date]
            && 'Client Interactions'[Interaction type ] = "complaint"
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Maggie, 

Thanks for your response. It needs a bit of tweaking I think. The solution will only provide the first interaction if the outcome date is present, also it seems to pull in those that don't match the interaction criteria "complaint". 

I was confused also about the syntax. My understanding was that the filter(allexcept(client record id) would return the min interaction date for each individual client and I would just need to combine this with something that asked to do this only when the interaction type matched complaint. So i wasn't sure why we needed to also filter between application and outcome dates also?

Thanks

[v-juanli-msft]

Hi @Anonymous 

So what is the correct result for your data example?

And what would be if outcome date is not there?

Best Regards

Maggie

[parry2k]

@Anonymous do you want to see each application of the client, I noticed there are two application for some of the client ?

[Anonymous]

Hi,

Thanks for looking into this.

yeah, each applicaiton will have a unique reference which ties to all interactions as well as outcomes.

So i'm looking for the date between the earliest date of complaint to the outcome for each applicaiton unique ID.

Hopefully that makese sense?

thansk

Euan