Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Employees headcount

Hello All, 

 

I am working on power bi and I am facing a challenge in calculating total number of employees, 

I have seen several articals in which they create inactive relationship between calendar and employee table. That works.

But my problem is I want to filter the data too and I have several other tables.

Employee table - Emplyee ID, start date and end date (if present else blank)

Timesheet table - This is my transaction table and I have several other tables as well like cost centre.

So, Timesheet is my transaction table and employee table is master's table.

Please do suggest me some other methods to make it work.

 

Below are the DAX which is used and they worked but for inactive relationship of employee and calendar table.

 
 
Headcount=CALCULATE(COUNTROWS(Employee),FILTER(VALUES(Employee[End_Date]),ISBLANK(Employee[End_Date])),FILTER(VALUES(Employee),[Start_date]<=MAX('Calendar'[Date])))
 
 
 
Eployee Joined =
var a = YEAR(MAX('Calendar'[Date]))
var b = MONTH(MAX('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee'),FILTER(VALUES('Employee'[Start_date]),YEAR('Employee'[Start_date])=a && MONTH('Employee'[Start_date])=b))
 
 
 
Employee Left = var a = YEAR(MIN('Calendar'[Date]))
var b = MONTH(MIN('Calendar'[Date]))
return
CALCULATE(COUNTROWS('Employee'),FILTER(VALUES('Employee'[End_Date]),YEAR('Employee'[End_Date])=a && MONTH('Employee'[End_Date])=b))
 
 

 

Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Actual Relations

       

       

      Left Side actual result, right side by using your DAX measures

       

       

      Hello amitchandak 

      Thankyou for your reply, I have attached 2 images, the first one shows the actual relations which i made and second one shows the results, 

      Results in left table are the actual results and right ones i created by using the dax which you shared.

      Please have a look what I atually want, and do suggest 

      Thanks!

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , can you share the formula you used. Do these dates have a timestamp. hire number seems off by far at few places.

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Try expanding your dates like in the video below, also see the attached for reference.

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn