cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freiburgc Regular Visitor
Regular Visitor

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 Super Contributor
Super Contributor

Re: Aggregated number of employees per date XXXX with powerpivot

Hi @freiburgc,

 

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

freiburgc Regular Visitor
Regular Visitor

Re: Aggregated number of employees per date XXXX with powerpivot

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.

 

grafik.pngResult based on your codegrafik.pngHow the result should look like...grafik.pngDatabase

 

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

freiburgc Regular Visitor
Regular Visitor

Re: Aggregated number of employees per date XXXX with powerpivot

HI @v-ljerr-msft,

 

thanks for your help!

 

Your code almost works.

 

 

grafik.pngDatabase

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

 

grafik.pngResult 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

 

freiburgc Regular Visitor
Regular Visitor

Re: Aggregated number of employees per date XXXX with powerpivot

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 321 members 2,960 guests
Please welcome our newest community members: