Forum Discussion

Elisa112's avatar
Elisa112
Icon for Helper V rankHelper V
2 years ago
Solved

Calculate date difference between different meeting types which are stored in the same column

Hello

I am trying to create a table to calculate the difference between different meeting dates per user (i,e days between intro and assess or intro and support, this must be on the first attended meeting.  I have tried to create a matrix but realise you cannot then add measures into the rows or columns.  My data looks like this:

 

MeetingTable

UserID      MeetingID    Meeting Type     Meeting date   Status

0011          0022             Intro                    01/12/2023    Attended

0011           0033            Assess                 03/12/2023    Attended

0011           0044            Support              04/12/2023     Cancelled

0011           0055            Support               05/12/2023     Attended

 

I have created this measure, but when added as a calculated measure to the above table, nothing appears

DateDiff =
VAR IntroductionDate = CALCULATE(MIN('Meeting List'[Date/Time]), 'Meeting List'[Type] = "Introductory Meeting")
VAR AssessDate = CALCULATE(MIN('Meeting List'[Date/Time]), 'Meeting List'[Type] = "Assessment")
RETURN
DATEDIFF(IntroductionDate, AssessDate, DAY)
Would it be better to create a summary table and add the measure to it, if so could you please provide some suggestions. At some point I will need to index all meetings per user as the data grows.
Thanks
Need Help
  • Elisa112's avatar
    Elisa112
    2 years ago

    ryan_mayu I have successfully added the attended meetings, there was a missing bracket and common which was causing the error.

    Thank you and I can now mark your solution as the accepted (and correct) solution

     

11 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    Elisa112 

    is this what you want?

    Table 2 = ADDCOLUMNS( ADDCOLUMNS(SUMMARIZE('Table','Table'[UserID ]),"introdate",maxx(FILTER('Table','Table'[UserID ]=[UserID ] && 'Table'[MeetingType]="Intro"),'Table'[Meetingdate]),"assessdate",maxx(FILTER('Table','Table'[UserID ]=[UserID ] && 'Table'[MeetingType]="Assess"),'Table'[Meetingdate])),"datedif",DATEDIFF([introdate],[assessdate],day))

    pls see the attachment below

    • Elisa112's avatar
      Elisa112
      Icon for Helper V rankHelper V
      2 years ago

      ryan_mayu thank you, your solution is bringing back the same dates and times for all users eg

       

       

      Any ideas on why, I assume because i do not have a calendar set up for testing but if you have any other suggestions, greatly appreciated. Im getting closer to solving this and I think your solution will work with some tweaking.

      Thanks in advance

       

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        Elisa112 

        modify the DAX, pls try this

         

        Table 2 = ADDCOLUMNS( ADDCOLUMNS(SUMMARIZE('Table','Table'[UserID ]),"introdate",maxx(FILTER('Table','Table'[UserID ]=EARLIER([UserID ] )&& 'Table'[MeetingType]="Intro"),'Table'[Meetingdate]),"assessdate",maxx(FILTER('Table','Table'[UserID ]=earlier([UserID ] )&& 'Table'[MeetingType]="Assess"),'Table'[Meetingdate])),"datedif",DATEDIFF([introdate],[assessdate],day))
        pls see the attachment below
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Elisa112 ,
    Here is my test data:

    Create a new table called SummaryTable

    SummaryTable = 
SUMMARIZECOLUMNS (
    'MeetingTable'[UserID],
    "DaysBetweenIntroAndAssess", DATEDIFF (
        CALCULATE ( MIN ( 'MeetingTable'[Meeting date] ) ),
        CALCULATE (
            MIN ( 'MeetingTable'[Meeting date] ),
            'MeetingTable'[Meeting Type] = "Assess"
                && 'MeetingTable'[Status] = "Attended"
        ),
        DAY
    ),
    "DaysBetweenIntroAndSupport", DATEDIFF (
        CALCULATE ( MIN ( 'MeetingTable'[Meeting date] ) ),
        CALCULATE (
            MIN ( 'MeetingTable'[Meeting date] ),
            'MeetingTable'[Meeting Type] = "Support"
                && 'MeetingTable'[Status] = "Attended"
        ),
        DAY
    )
)

    Final output

     

    Best regards

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
     

    • Elisa112's avatar
      Elisa112
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous 

      Tried this and my summary table is coming back completely blank, any idea why that would be?

      I feel I am missing something so obvious

      thanks in advance

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Elisa112 ,
        You can refer to the pbix file I provided. Regarding why there are blank data, you can go and check your raw data to see if there are inconsistencies with the matching fields, for example, if there are spaces before and after the raw data that would cause a blank summary to appear

        Please check these fields for any discrepancies, especially spaces.

         

        Best regards

        Albert He

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly