cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Calculating monthly employees

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.

1 ACCEPTED SOLUTION
Helper II

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

4 REPLIES 4
Community Support

Hi

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

Helper II

Hi

Helper II

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.

Helper II

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