Table with 2 columns, employment and detremination date of employees. My Report should show me employees who still work for the company at end of 2017

[Tendo]

Hello,

I´m new to this forum and powerpivot as well. I hope somebody can help me with my little powerpivot issue.

I have a 4 different tables.

The first one "Resource" includes 3 different columns, Name of employee, Employment Date & Termination Date.

The second one "SP_Day" includes the columns, name of employee,activity date, hours of overtime.

The third table contains name of employee, Job Description, Quantity.

The last one is the calendar table.

I created a dashboard which has two reports. The first one shows sickness absence rate for each employee and the second one the hours of overtime. The values of each report is fine. But my problem is, that they show each employee in the history of the company. I added a timeline slicer to show me just the employee for example at the end of 2017, it doesn´t work.

Can anybody help me to solve my problem?

I hope I explained clear enough the probleme I have. Sorry my bad explanaition and even worse english, I´m german.

Thanks for every answer and help!

Tendo

 

[gazpage]

May not be the easiest thing for some one new to Power Pivot.

If you wanted to count the number of employees at end of 2017, you would want something like.

Measure:=
COUNTROWS ( 
    Filter (
        Resource,
        Resource[Employment Date] <="31/12/2017"
            && Resource[Termination Date] >"31/12/2017"
    )
)

Depending on the relationships in your model and how you need the filters to flow, you should be able to use CALCULATETABLE with the filter part above to get what you need.

 

[Tendo]

Hello gazpage,

thanks for your answer.

That´s not what I need, sorry.

For example, if an employee A works from 2015 to 2017 and employee B from 2000 to 2018 for the company. I got the sickness rate of both. If I have a look at the end of 2017, I want to the sickness rate of employee A & B. By the end of 2018 I just want to see employee B. I would like to use a timeline slicer and have a second report for overtime. I have a sheet with both reports, but they doesn´t work with the timeline slicer. It doesn´t matter what time I have on the slicer. The reports show every employye.

I hope, I could make more clear the problems I have my reports.

I would like to uploaded a test data set, but can´t find "Add button" or something...

Thanks
Tendo

 

[gazpage]

Google and use a "Disconnected Slicer". Formula would then be.

Measure:=
COUNTROWS ( 
    Filter (
        Resource,
        Resource[Employment Date] <=MAX ( Disconnected[Dates] )
            && Resource[Termination Date] > MAX ( Disconnected[Dates] )
    )
)

This assumes that the last date selected in your slicer is the important date, but you could switch a MAX to a MIN if you wanted the number of employees that were employed at any point in the selected dates.