DATEDIFF between 2 dates in different tables

[Anonymous]

Hi this is my very first post and hoping someone can guide me in the right direction.

I'm trying to ascertain the DATEDIFF in minutes between 2 dates that are held in different tables.

My first table contains operating theatre sessions.  I've created a composite key with the date of the session, the operating theatre, and which surgeon is scheduled to use it.

My second table contains the patient-specific information for surgeries.  Again I created a composite key with the date of surgery, the operating theatre where the surgery was performed, and the surgeon who performed it.  I have also ascertained which patient per date, per operating room per surgeon was the first to be operated on. 

Just to note, I also have a 'link' table with the data for each dimension (operating room and surgeon)

What I'm trying to measure is the number of minutes between the start of the session (START_DTTM from table 1) to the start of the first patient (Into Theatre DateTime from table 2) where the %Key matches.

After discovering various different functions, I thought I had a breakthrough with the below code but it doesn't work when trying to aggregate over a number of days/weeks obviously since I haven't defined that the 'fact_service_point_sessions'[%Key] needs to equal the 'fact day therapy theatre events'[%Key].

Minutes = CALCULATE(DATEDIFF(SELECTEDVALUE('fact_service_point_sessions'[START_DTTM]),SELECTEDVALUE('fact day therapy theatre events'[Earliest Date]),MINUTE), 'fact day therapy theatre events'[First Patient] = "First")

Open to any and all suggestions/advice.

Thanks if you got this far!

Ciara

[Anonymous]

Hi Tamer

Thanks for responding.  

Below is an expanded matrix showing the measure I'm expecting for the example I gave above. 

But if I collapse any portion of the matrix it doesnt aggregate my measure.

[tamerj1 · Accepted solution]

@Anonymous 

Please use 

Minutes =
SUMX (
    CROSSJOIN (
        SUMMARIZE ( 'Link Table', dim_date[Date], dim_date[Year] ),
        SUMMARIZE (
            'Link Table',
            'Link Table'[dim_professional_carer_key],
            'Link Table'[dim_service_points_key]
        )
    ),
    CALCULATE (
        DATEDIFF (
            SELECTEDVALUE ( 'fact_service_point_sessions'[START_DTTM] ),
            SELECTEDVALUE ( 'fact day therapy theatre events'[Earliest Date] ),
            MINUTE
        ),
        'fact day therapy theatre events'[First Patient] = "First"
    )
)

[Anonymous]

Thank you so much for all your help Tamer.  I couldn't have done it without you.

[tamerj1]

Hi @Anonymous 

Can you please share a sample of the expected results?