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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
swatsonlord
Regular Visitor

Distinct Count of meetings attended after employee start date

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_NameStart_DateDate of DeliveryExpected Outcome of formula 
Staff 1 1/01/20001/09/2023 
Staff 1 1/01/20001/10/2023 
Staff 1 1/01/20001/11/2023 
Staff 2 15/10/2023 1
Staff 3 10/09/2023 2
Staff 4 2/10/20231/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)
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1698657013022.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1698657013022.png

 

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

Thank you, that worked! 

swatsonlord
Regular Visitor

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

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.