Show a list with employees at specific date

[Anonymous]

Hello,

I have a problem with a report in power bi I try to solve for weeks.

My problem is that I need a list which shows the employees of the company I work for at specific date.

This is an example data set:

And I have Calender Table. But I don´t know if I need the calender table to make my report work or not.

If choose for example 31.12.2017 on the slice, the list in my report should show me people who still working in the company.

The people are: Hans, Alexandra and Mario

30.04.2018: Hans, Alexandra and Mario

I have found some solutions in the forum. Those solutions use measure that shows in new a column "1" if the employee is still working for the company. This way would help me to count the employees. 

I hope my explaination is clear enough! 🙂

Does anybody can help me to find a solution for my problem or already have one?

I´m thankfull for every little help!

cfreiburg

[MFelix]

Hi @Anonymous,

Believe that your data is not matching you result because on 31/12/2017 you have Hans, Peter, Alexamdre; Michelle and Mario working in the company and on 30/04/2018 the names are Alexandra, Michelle, Mario and Tony on your dataset however create the following measure and add it to your visual as a filter:

Working =
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE ();
        MIN ( Employees[Start Date] ) <= Selected_Date
            && MAX ( Employees[End Date] ) >= Selected_Date; "working";
        MIN ( Employees[Start Date] ) <= Selected_Date
            && MAX ( Employees[End Date] ) = BLANK (); "Working";
        "Not working"
    )

Then set the filter to "Working".

You can also use an interval of dates instead of a specific date.

Regards,

MFelix

[Anonymous]

Hi @MFelix,

Could you help me, please?

I used your solution to filter a table with employees' start and leave dates. It works perfectly.

But I'd also like the number of employees shown in the table visual to be displayed at the card visual. Applying the same filter to the card doesn't help.

I also tried to create a different one for the card: 

Measure = CALCULATE(COUNT(employee_lifecycle[employee_code]),FILTER(employee_lifecycle, employee_lifecycle[Working]="Working"))

But it leads to an error.

Is there any way to show the number of records on a card after applying a filter?

Thanks in advance!

[MFelix]

Hi @Anonymous ,

Try the following measure for the count of the working to put on a card visual:

Measure = COUNTROWS(FILTER(ALL(employee_lifecycle[employee_code]);[Working] = "Working"))

Regards,

MFelix 

[Romenskyi]

Hi @MFelix 

It really helped a lot! Thank you!

But I am struggling to continue using the filtered table with the data in the related tables

What if I wanted to create a histogram (or any other visual) showing the resulted data but filtered by location for instance? Is it even possible?

Here are the screenshots of the relations and how I imagine the histogram to look like. I tried every possible way I know but I only could get it to work with all the records in the table, without the "Working" measure as a filter

Could you please help? Thanks again!

 

[Romenskyi]

Please disregard

I made it work by switching from DirectQuery to Import

Thanks anyway!

[Anonymous]

@MFelix ,

It works. Thanks a lot!

[Anonymous]

Hello @MFelix,

that exactly what I need! Thank you very much!!!!! 🙂

Regards,

freiburgc