Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATEDIFF between 2 dates in different tables

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
  • tamerj1's avatar
    tamerj1
    4 years ago

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

     

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    Can you please share a sample of the expected results?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

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