Employee promotion count

[Anonymous]

Hi,

I am trying to count the number of month that pass before an employee gets promoted. I use this calculated column to count the rows. The issue I have is that I would like for the counter to increase by one. In Jan-19 1, Feb-19 2 and so on until the promotion but right now is only counting.

MESES SIN CAMBIO DE PUESTO =
CALCULATE (
COUNTROWS ( EMPLEADO ),
FILTER (
EMPLEADO,
EMPLEADO[nro_documento] = EARLIER ( EMPLEADO[nro_documento] )
&& EMPLEADO[nombre_puesto] = EARLIER ( EMPLEADO[nombre_puesto] )
)
)

Please your help.

[v-kelly-msft]

Hi @Anonymous,

You can create a calulated column as below:

Column = 
var a =CALCULATE(COUNTROWS('EMPLEADO'),FILTER('EMPLEADO','EMPLEADO'[documento]=EARLIER(EMPLEADO[documento])&&'EMPLEADO'[nombre]<=EARLIER(EMPLEADO[nombre])&&'EMPLEADO'[status]<>"promoted"))
Return
a

And you will see:

Here I didnt count in the row which marks "promoted",if you need to count this row in,just remove the criteria in the dax expression.

Or you can create a measure as below:

Measure = 
var a =CALCULATE(COUNTROWS('EMPLEADO'),FILTER(ALL('EMPLEADO'),'EMPLEADO'[documento]=SELECTEDVALUE(EMPLEADO[documento])&&'EMPLEADO'[nombre]<=SELECTEDVALUE(EMPLEADO[nombre])&&'EMPLEADO'[status]<>"promoted"))
Return
a

And you will see:

Here is a sample .pbix file you can refer to.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft,

I am attaching a table with the expected result. The issue I have is that I don't have a column with the status of the change. That's why I have the calculated column that counts when the name of the position changes as a promotion. In Now it shows how it's working now, it counts all the rows with the same name of position. I would like to count it for each month until now.

nro_documento	mes_año	nombre_puesto	Calculated Column	Now
1	1/1/2020	A	1	1
1	2/1/2020	B	1	3
1	3/1/2020	B	2	3
1	4/1/2020	B	3	3
2	2/1/2020	C	1	2
2	3/1/2020	C	2	2
2	4/1/2020	D	1	1
3	3/1/2020	E	1	2
3	4/1/2020	E	2	2

Thanks for all the help.

[Anonymous · Accepted solution]

Hi,

I managed the formula

MESES SIN CAMBIO DE PUESTO =
CALCULATE (
COUNTROWS ( EMPLEADO ),
FILTER (
EMPLEADO,
EMPLEADO[nro_documento] = EARLIER ( EMPLEADO[nro_documento] )
&& EMPLEADO[nombre_puesto] = EARLIER ( EMPLEADO[nombre_puesto] )
&& EMPLEADO[Mes Año] >= EARLIER ( EMPLEADO[Mes Año] )
)
)

 Thanks for all the help 

[amitchandak]

@Anonymous , It is very difficult without sample data.

Can you share sample data and sample output.