COUNT Number of employees left in this Month(MTD) and this Year(YTD)

[Anonymous]

I am trying to count the number of employees who left in the Month we are currently we are in as well as the year we are in. Assuming we are in November 2022.   The Data is as follows

Employee		Date Left
EMployee1		12/11/2022
Employee2		06/10/2022
Employee3		01/07/2022
Employee4		12/08/2022
Employee5		18/06/2022
Employee6		22/05/2022

Only EMployee 1 has left in November 2022 so for the month, it should return 1 , and for the Year count, it should return 6 Employees since all of them left in the year 2022. How do I achieve this?

[Mikelytics]

Hi  @Anonymous ,

Please try the following measure (I assume you also use a separate date table?):

Number of Employees Left =

CALCULATE(
   COUNTROWS(Table),
   FILTER(
      Table,
      Table[Date Left] <= MAX(Date[Date])
   )
)

If this does not work some questions.

Can you please put it in a visual and use a slicer like you described in your request to show which values do not meet your expectation?

Are in your table also employees who did not leave?

Is in the table each row one employee or are there some other relevant columns regarding

Are there other Measures you use in combination with this one?

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

 granularity?

[vojtechsima · Accepted solution]

Hi, @Anonymous 
Like this:

Employee Left MTD = 
CALCULATE(
    COUNTROWS('Table'),
    DATESMTD('Table'[Date Left])
)

Employee Left YTD = 
CALCULATE(
    COUNTROWS('Table'),
    DATESYTD('Table'[Date Left])
)

I assume your table contains one row = one employee, and for the column for DATESYTD/MTD, you should preferably use your Calendar date dolumn.
If this doesn't work add slicer with context filter context, such as month etc.