Forum Discussion
Helper IICalculating monthly employees
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 )))
Community SupportHi 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
Wiene24Hi
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-date/m-p/180365
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
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 )))