Calculating monthly employees

[Wiene24]

 Hi,

I would like to calculate the monthly active employees.

I have a colom with a startdate, I have a colom with an enddate (if they became inactive) and I need to use employee numbers under 1000

I have tried:

Aantal actieve medewerkers =
VAR currentDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE(COUNTROWS('dba Medewerker');FILTER('dba Medewerker';'dba Medewerker'[DatumInDienst] <=currentDate && 'dba Medewerker'[DatumUitDienst reken] >= currentDate && 'dba Medewerker'[PersoneelsNummer] < 1000))

Found this on here, it doesnt give me an error but if I use it it does give me an error with a calculating error.

[v-yuta-msft]

Hi Wiene24,

It seems like that you haven't build a relationship between Data table and dba Medewerker table, please check if you have build a relationship between the two table. After building the relationship , you can use related() function to call other tables' columns.

Regards,

Jimmy Tao

[Wiene24]

Hi 

I have created 2 different data tables because I have to connect DatumInDienst (translated DateInService), DatumUitDienstReken (translated DateOutService)

Both I have connected to one date table.

But wich one do I have to use for:

VAR currentDate =
MAX ( 'Date1'[Date] )

BTW I used this topic as reference: https://community.powerbi.com/t5/Desktop/Calculating-a-monthly-employee-count-from-a-start-and-end-d...

Here it says that you don't have to build a relationship with the dates.

Kind Regards,

Tim Wijnen

[Wiene24]

I fixed the error what I got when I tried to use it in a diagram, but the final result now is that I see how many people joined the company in what year/month.

So still don't have the active people on a monthly base.

[Wiene24 · Accepted solution]

It was eventualy fixed with the following formula:

Aantal actieve medewerkers per maand =
VAR currentDate =
    MAX ( Datumtabel[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'dba Medewerker' );
        FILTER (
            'dba Medewerker';
            ( 'dba Medewerker'[DatumInDienst] <= currentDate
                && 'dba Medewerker'[Datumuitdienstnieuw] >= currentDate )))