## Desktop

Frequent Visitor
Posts: 9
Registered: ‎12-18-2018

# active workers

hi, i'm trying to display the number of workers today, I have a start date and a leaving date, so i need to show today's active workers and then with a slicer I want to show the same information by month, hope someone can help me.
example data:

 Worker ID start date leaving date 19311196 2003-06-15 2018-01-24 11424431 2005-07-26 2018-01-28 10574322 2009-09-17 2018-01-31 10240660 2001-10-03 18264874 2001-07-28 17692937 2004-10-02 12298585 2011-07-03

(date yyyy-mm-dd)

the ones with no leaving date, means that are still working on the company.
thank you

Accepted Solutions
Highlighted
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

## Re: active workers

Hi @MarcoLopez,

Sample for your refernce, please check the following steps as below. If I misunderstood your request, kindly share your excepted result to me.

1. Crete a date table and one calculated column in it.

`date = CALENDARAUTO()`
`YearMonth = YEAR('date'[Date]) &" " &FORMAT('date'[Date],"mmm")`

2. Create two measures to get the result as we need.

```today's active workers =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALL ( Table1 ), Table1[leaving date] = BLANK () )
)
```
```by month =
VAR mindate =
CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date'[YearMonth] ) )
RETURN
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
OR (
Table1[start date] <= mindate
&& Table1[leaving date] >= mindate,
Table1[start date] <= mindate
&& ISBLANK ( Table1[leaving date] )
)
)
)
```

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

All Replies
Highlighted
Community Support Team
Posts: 1,997
Registered: ‎07-10-2018

## Re: active workers

Hi @MarcoLopez,

Sample for your refernce, please check the following steps as below. If I misunderstood your request, kindly share your excepted result to me.

1. Crete a date table and one calculated column in it.

`date = CALENDARAUTO()`
`YearMonth = YEAR('date'[Date]) &" " &FORMAT('date'[Date],"mmm")`

2. Create two measures to get the result as we need.

```today's active workers =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALL ( Table1 ), Table1[leaving date] = BLANK () )
)
```
```by month =
VAR mindate =
CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date'[YearMonth] ) )
RETURN
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
Table1,
OR (
Table1[start date] <= mindate
&& Table1[leaving date] >= mindate,
Table1[start date] <= mindate
&& ISBLANK ( Table1[leaving date] )
)
)
)
```

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.