Distinct Count of meetings attended after employee start date

[swatsonlord]

Hey There, 

I am trying to calculate how many meetings an employee has missed after their start date. I will then be putting this into a matrix table to show how many meetings each person has missed since they started.

Below is a sample of my data and the expected outcome of the formula: 

If date of delivery is blank they have not attended a meeting

Full_Name	Start_Date	Date of Delivery	Expected Outcome of formula
Staff 1	1/01/2000	1/09/2023
Staff 1	1/01/2000	1/10/2023
Staff 1	1/01/2000	1/11/2023
Staff 2	15/10/2023		1
Staff 3	10/09/2023		2
Staff 4	2/10/2023	1/11/2023

Any help would be appreciated!

I have tried a few version of the below formula but it seems to be ignoring the filter and is bringing back the total number of meetings held regardless of the start date: 

Missed Meetings = IF(sharepointWorkdayStaffList[Date of Delivery]=BLANK(),CALCULATE(DISTINCTCOUNTNOBLANK(sharepointWorkdayStaffList[Date of Delivery]),FILTER(sharepointWorkdayStaffList,sharepointWorkdayStaffList[Date of Delivery]>sharepointWorkdayStaffList[Start_Date])),0)

[v-yangliu-msft · Accepted solution]

Hi  @swatsonlord ,

Here are the steps you can follow：

1. Create calculated column.

Expected Outcome of formula =
var _mindate=MINX(FILTER(ALL('Table'),'Table'[Full_Name]=EARLIER('Table'[Full_Name])),[Start_Date])
return
IF(
'Table'[Date of Delivery]=BLANK(),
CALCULATE(
    DISTINCTCOUNT(
        'Table'[Date of Delivery]),
        FILTER(ALL('Table'),
        'Table'[Date of Delivery]<>BLANK()&&_mindate<'Table'[Date of Delivery])),
        BLANK())

2. Result:

Best Regards,

Liu Yang

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

[swatsonlord]

Thank you, that worked! 

[swatsonlord]

Hey Sahir, 

Thanks for the formula - however that doesn't quite meet my needs, it brings back a 0 when there is no date of delivery and 1 if there is a date, however I need to have a count of the meetings missed if there is no delivery date. 

Thanks,

[Sahir_Maharaj]

Hello @swatsonlord,

Can you please try this:

Missed Meetings = 
VAR EmployeeStart = MIN(sharepointWorkdayStaffList[Start_Date])
RETURN
IF(
    ISBLANK(sharepointWorkdayStaffList[Date of Delivery]), 0,
    IF(
        sharepointWorkdayStaffList[Date of Delivery] > EmployeeStart,
        1,
        0
    )
)