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

[Elisa112]

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

[ryan_mayu · Accepted solution]

@Elisa112 

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

[v-heq-msft]

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]

Hi @v-heq-msft 

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

[v-heq-msft]

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

[ryan_mayu]

@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]

@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]

@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]

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 · Accepted solution]

@Elisa112 

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

[Elisa112 · Accepted solution]

@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

[Elisa112]

@ryan_mayu thank you so much, this is now working with just 1 exception, I need to bring back the first attended meeting, sometimes a user fails to attend and even though this is the first recorded meeting, the only date used in the KPI is the first attended.  I have tried to add && MeetingStatus = attended but this throws an error. Any ideas most welcome! thanks again

[Elisa112]

@ryan_mayu thank you, the adjusted code returns

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