Enquiry Response Time using Datediff where the Dates are in Different Tables

[HabibAdil]

Hi, 

I am trying to calculate monthly average enquiry response time in days, but unable to do so. Please can you help? In the attached example, an opportunity (Opp) can have more than 1 quotes. The response time is based on the when the opp was created and when the first quote was sent. The tables are joined using Opp GUID field. 

Just to add a bit of extra complexity, but not essential. One quote can be assosicated to more than 1 Opp as well.

[v-eachen-msft · Accepted solution]

Hi @HabibAdil ,

You could create a rank column at first.

Rank =
RANKX (
    FILTER ( Quote, Quote[GUID] = EARLIER ( Quote[GUID] ) ),
    Quote[Date],
    ,
    ASC,
    DENSE
)

Then use LOOKUPVALUE() or RELATED() function to match the resuilt.

Result1 =
VAR a =
    LOOKUPVALUE ( Opp[Date], Opp[GUID], Quote[GUID] )
RETURN
    IF ( Quote[Rank] = 1, DATEDIFF ( a, Quote[Date], DAY ) )

Result2 =
VAR a =
    RELATED ( Opp[Date] )
RETURN
    IF ( Quote[Rank] = 1, DATEDIFF ( a, Quote[Date], DAY ) )

Then show the average in the visual.

Here is my test file for your reference.

[HabibAdil]

Hi @v-eachen-msft, works great. Thanks for your help!!!

[Greg_Deckler]

You could use LOOKUPVALUE or perhaps take a look at this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[HabibAdil]

Hi @Greg_Deckler Thanks for the help. Unfortunately It did not work for me.

[Greg_Deckler]

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490