topic Re: DATEDIFF between 2 dates in different tables in DAX Commands and Tips

DATEDIFF between 2 dates in different tables

Anonymous — Wed, 25 May 2022 12:36:13 GMT

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

Re: DATEDIFF between 2 dates in different tables

tamerj1 — Wed, 25 May 2022 12:54:08 GMT

Hi Anonymous

Can you please share a sample of the expected results?

Re: DATEDIFF between 2 dates in different tables

Anonymous — Wed, 25 May 2022 14:06:12 GMT

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.

Re: DATEDIFF between 2 dates in different tables

tamerj1 — Wed, 25 May 2022 15:49:02 GMT

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" ) )

Re: DATEDIFF between 2 dates in different tables

Anonymous — Thu, 26 May 2022 13:51:38 GMT

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