Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
The table "Heacount" looks like below:
Edition date | Employee number | CC | Act/For | New | Quit | Joined CC | Left CC | Changed CC | Month |
01.06.2022 | 6 | ||||||||
01.07.2022 | 4871 | NOHA303971 | Actual | New | Quit | 7 | |||
01.07.2022 | 4688 | NOHA000993 | Actual | New | 7 | ||||
01.08.2022 | 4688 | NOHA000993 | Actual | 8 | |||||
01.09.2022 | 4871 | NOHA303971 | Actual | New | 9 | ||||
01.09.2022 | 4688 | NOHA000993 | Actual | Quit | 9 | ||||
01.10.2022 | 4871 | NOHA303971 | Actual | Quit | 10 | ||||
01.11.2022 | 4688 | NOHA000993 | Actual | New | From NOHA000993 to NOCS000862 | 11 | |||
01.12.2022 | 4688 | NOCS000862 | Actual | From NOHA000993 to NOCS000862 | Changed CC | 12 | |||
01.12.2022 | 4871 | NOHA000850 | Actual | New | 12 | ||||
01.01.2023 | 4871 | NOHA000850 | Forecast | Quit | 1 | ||||
01.03.2023 | 4871 | NOHA000850 | Forecast | New | From NOHA000850 to NOHA000851 | 3 | |||
01.04.2023 | 8888 | NOHA000993 | Forecast | New | 4 | ||||
01.04.2023 | 4871 | NOHA000851 | Forecast | From NOHA000850 to NOHA000851 | Changed CC | 4 | |||
01.04.2023 | 4871 | NOHA000851 | Forecast | 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.
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] )
)
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.
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |