Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Wiene24
Helper II
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

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

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

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

Hi 

 

 

 

 

 

 

 

 

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.

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.