Count employee by Department by Year

[Anonymous]

Hi Everyone

I am new to PowerBI, so please be nice to me 🙂

I am trying to write a report to show "Employee Count by Department by Year".  Below is my data from the HR system.  It doesn not break down Department (OBSDescriptions) yearly.  If the employee transfered to the new department, then HR processed the transfered date (OBS_effEndDate).   How do I create a report similar to the one I have below?

[Anonymous]

Happen to know if you have access to Power Query and the ability to bring in that data?  Even if its from the excel output below. Data really reads to be in tabular format (think more rows than columns). That's the first step before you want to try authoring DAX. 

[Anonymous]

Hi Nick,
Thank you.. My data is already in tabular format. I connect data from the accounting system through ODATA which I manipulate further in power query.

[Anonymous · Accepted solution]

If anyone need the solution.  https://forum.powerpivotpro.com/forums/topic/calculate-headcount-based-start-end-date-range/