cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Aggregated number of employees per date XXXX with powerpivot

Hello,

 

I need your help please. I´m new to PowerPivot & DAX.

 

My goal is to have all employees in a chart by 31.12.2017 or another date based on powerpivot datamodel. I also would like to use timeline to filter dates.

I have an excel file which contains two different tables.

First table contains in a column name of employees, second column is employment date and third one is termination date.

Secoand table contains a calender.

 

 The chart should show the aggregate employyes by date XXXXX.

 

I would like to add an example file, but can´t. Is it possible?

 

Sorry for my bad english (i´m german) and even more worst explanation.

 

Thanks a lot!!

 

 freiburgc

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

Hi @Anonymous,

 

If I understand you correctly, you should be able to firstly use the formula below to create a new measure.

Count of Employees =
VAR currentDate =
    MAX ( 'Table2'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            ( Table1[employment date] <= currentDate
                && Table1[termination date] >= currentDate )
        )
    )

Note: You'll need to replace 'Table1' and 'Table2' with your real table name. Also make sure there is no any relationship between  'Table1' and 'Table2'.

 

Then show the measure as Values, the Table2[Date] column as Axis on a chart visual to get the expected result in your scenario. Smiley Happy

 

Regards

Anonymous
Not applicable

HI @v-ljerr-msft,

 

thanks for your help!

 

Your code almost works.

 

 

DatabaseDatabase

If column "Termination date" is empty the employee isn´t counted.

 

Result based on your codeResult based on your code

The database includes Francois Holland, David Cameron and Mark Rutte. All of them were hired in 2016 and still in the company. But they aren´t listed in the pivottable "Result based on your code", because of the empty column "Termination Date".

 

Can your help me again to solve that problem. I have no clue why...

 

Thx again for your support!!

 

Christian

 

Anonymous
Not applicable

Hi @v-ljerr-msft,

 

I found a solution for the blank cells in the column "Termination Date".

 

Somebody had already the same problem and solve it.

 

So i used his code:

 

Mitarbeiterper:=VAR _dateofperiod=LASTDATE('Tab_Kalender_2'[Datum])RETURN(COUNTROWS(FILTER('Tab_Resource_2';VAR hireDate='Tab_Resource_2'[Employment Date] VAR termDate=IF(ISBLANK(Tab_Resource_2[Termination Date]);DATE(2099;1;1);Tab_Resource_2[Termination Date])RETURN hireDate<=_dateofperiod && termDate>=_dateofperiod)))

But i still have the issue with relationship between the to tables. Do you have an idea to solve my problem?

 

Thx

freiburgc

Anonymous
Not applicable

HI @v-ljerr-msft

 

thanks for your help!

 

Why no relationships between the two tables? I have one and need them for another measure, which I calculate with those tables.

 

Result based on your codeResult based on your codeHow the result should look like...How the result should look like...DatabaseDatabase

 

The photo "How the result should look like..." shows the accumulated numbers of employees by the End of each year. If I use your code I get the number of employees who were hired in this year. BUT -1, that means in 2015, three people were hired, the chart shows only 2. The same for the other years.

 

I have no idea why! Could you be so kind and help me!!

 

Thx again!

 

Christian

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors