Employee count by date

DAX DAX Calculations
1

Hi guys

I am struggling to understand how I can visualise a count of current employees over date that is sliceable, as each employee has both a start and end date (if they have now left the business)

At each month end I need to be able to show the number of employees within the company

I have achieved a global count by creating a column within my dates table as below:

Employee Count =
CALCULATE (
    DISTINCTCOUNT ( 'Employee Details'[NI Number] ),
    FILTER (
        'Employee Details',
        'Employee Details'[Employment Started On] <= Dates[Date]
    ),
    FILTER (
        'Employee Details',
        OR (
            'Employee Details'[Employment Ended On] > Dates[Date],
            ISBLANK ( 'Employee Details'[Employment Ended On] )
        )))

However, as this column is stored within my Dates table I am unable to filter the result by columns in my Employee Details table as required

For example - I want to split the global count by department or by staff level

Any suggestions as to where I am going wrong? I have searched through the resources and cannot find a solution

Cheers

Dave

1 Like
2

Hi Dave, based on what you’re saying you should be able to filter by a dimension in your ‘details’ table so there’s something missing.

Mind placing some pics of the model and formula.

Also have you got the formula from the events in progress tutorial - http://portal.enterprisedna.co/courses/solving-business-scenarios-with-power-bi-and-dax/lectures/2217176

Also for example this example here showcases a similar technique (whilst it’s an insurance example and ‘open applications’) but still allows other filters to be applied, so that’s why it must be something else here.

Sam

1 Like
3

Thanks Sam

That video sorted my issue, I hadn’t quite made it as far as that in my learning and didn’t realise both relationships from the date table needed to be inactive and then referred to in the measures

Great stuff - my model is now much simpler and works as expected

Dave

4

Ok great, yes setting up the model here in that unique way is important to get this working.

5

Hi Sam
Can you explain how you shared the Power BI Report as posted here? I was able to open and browse in Chrome and all worked well without it opening Power BI etc. Thanks John

6

This is just a ‘publish to web’ link. I just copied one of the report links from the Enterprise DNA Showcase page into here. Same thing