Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Elisa112
Helper II
Helper II

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
2 ACCEPTED SOLUTIONS

@Elisa112 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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

 

View solution in original post

11 REPLIES 11
v-heq-msft
Community Support
Community Support

Hi @Elisa112 ,
Here is my test data:

vheqmsft_0-1705638895712.png

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

vheqmsft_1-1705639041815.png

 

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
 

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

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

vheqmsft_0-1705889021739.png

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
Super User
Super User

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

11.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Elisa112_0-1705665222675.png

 

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

 

@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))
11.PNG
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello @ryan_mayu 

I tinkered a little bit more and it works!

Elisa112_0-1705946856364.png

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

@Elisa112 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@ryan_mayu thank you, the adjusted code returns

 

Elisa112_0-1705919064453.png

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

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.