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 m...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Elisa112 

    if you want to get the first attended meeting, try to change maxx to minx

  • 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

     

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
    • Elisa112's avatar
      Elisa112
      Icon for Helper V rankHelper V
      2 years ago

      ryan_mayu thank you, the adjusted code returns

       

       omitting the assessdate and date diff, any further suggestions greatly appreciated

       

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

      Hello ryan_mayu 

      I tinkered a little bit more and it works!

      The problem I need to figure out is how to bring back only the first attended meeting for any meeting type. I am so pleased to have got this far, do you think I need to change the maxx filter to min as I dont think I can add another expression to the filter.  any further help appreciated, 

      thank you

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

        Elisa112 

        if you want to get the first attended meeting, try to change maxx to minx