Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jbageneau
Frequent Visitor

Headcount

Topic can be closed, I found a workaround.

Hello,

 

I am struggling to create the measures that I want. Any help is more than welcome.
It is yet again a headcount problem. I have tried several solutions that I saw on this forum, to no avail.

I am using this summary table, simply called "Headcount" and a calendar table, called "2 - Calendar" with unique dates starting from 01.02.2022.
The two tables are linked through a one to many relationship between "Edition date/ Date". Other tables are linked this way, but I do not think they are relevant here. And the level of details I need is only by month.

jbageneau_2-1674223328966.png

jbageneau_3-1674225480267.png

 

The table "Heacount" looks like below:

Edition dateEmployee numberCCAct/ForNewQuitJoined CCLeft CCChanged CCMonth
01.06.2022        6
01.07.20224871NOHA303971ActualNewQuit   7
01.07.20224688NOHA000993ActualNew    7
01.08.20224688NOHA000993Actual     8
01.09.20224871NOHA303971ActualNew    9
01.09.20224688NOHA000993Actual Quit   9
01.10.20224871NOHA303971Actual Quit   10
01.11.20224688NOHA000993ActualNew  From NOHA000993 to NOCS000862 11
01.12.20224688NOCS000862Actual  From NOHA000993 to NOCS000862 Changed CC12
01.12.20224871NOHA000850ActualNew    12
01.01.20234871NOHA000850Forecast Quit   1
01.03.20234871NOHA000850ForecastNew  From NOHA000850 to NOHA000851 3
01.04.20238888NOHA000993ForecastNew    4
01.04.20234871NOHA000851Forecast  From NOHA000850 to NOHA000851 Changed CC4
01.04.20234871NOHA000851Forecast Quit   4

 

It is weird in the sense that "Quit" and "Left CC" display a text for the month to come.
Someone who "quitted" in July would be away from August and someone who "Left CC" in the November would actually leave the CC in December.

"New" and "Joined CC" are active on the correct lines.


I would like to have the number of employees starting or quitting per month, as well as the total number of employee per month, and this would be the most important.
I have managed to get the number of employees started with a simple CALCULATE(COUNT,FILTER), but struggle with the rest.

  • For the employee quitting, the "Quit" text in on the previous month. The closest I have been is this measure. It displays values for the right month but ignores the filter on "Quit".

I managed to find a solution for "Quit" measure by using:

 

 

Quit =
CALCULATE (
    COUNT ( 'Table'[Edition date] ),
    KEEPFILTERS ( 'Table'[Quit] IN { "Quit" } ),
    PREVIOUSMONTH ( '2 - Calendar'[Date] )
)

 

 

  • For the number of employee per month, I have no clue where to start. All ideas are welcome. The idea is to count 1 for each month an employee is employed, from the month he is new, to the month before he quits.

Thank you by advance for your help.
Also it my first post here, I tried to follow the guide lines much as I could, but please tell me if I missing something.

 

 

1 REPLY 1
MayaraRego
Regular Visitor

Oie @jbageneau , qual foi a solução que você conseguiu encontrar?

Estou com um problema parecido, no entanto eu preciso trazer o Headcount por Município.

 

Obrigada.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors