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

     

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.