HELP WITH DAX - DATE CALCULATION CONDITION

[LeonardCs]

I have a calendar table and a table with all the employees of the company. In this employees table, there are details like employee ID, admission date, and dismissal date for those who have already been dismissed. Those who have not been dismissed yet have a blank dismissal date.

I need to calculate three indexes:

A - Total employees hired during the period (I have already calculated this)

B - Total employees dismissed during the period (I have already calculated this)

C - Total active employees during the period (I am unable to calculate this)

For example, if I have a filter for the year 2025, I need to know how many employees were active in 2025. If I select the year 2024, I need to know how many employees were active in 2024.

How can I do this in DAX?

Please note the following conditions:

The employee's admission date must be less than or equal to the date selected in the filter of the calendar table.
The employee's dismissal date must be greater than or equal to the date selected in the calendar table filter, or the dismissal date can be blank.
I am sending the .pbix file as an example of what I need.

I would be very grateful if you could help.

Thank you

https://drive.google.com/drive/folders/1nAGe89KiFBD3DYaTaGJylqa9O-qpELD_?usp=drive_link

[lbendlin · Accepted solution]

Your formulas can be simplified

A_admissoes = COUNTROWS(TURNOVER)

B_demissoes = CALCULATE(COUNTROWS(TURNOVER), USERELATIONSHIP(CALENDARIO[Date], TURNOVER[DATA_DEMISSÃO]))

For the C measure you want to be very specific with your requirement. "Total active employees during the period "  is too fluffy. Better would be to say "Total number of employees who were active on at least one day of the period.

C_ativosNoPeríodo = 
var d = values(CALENDARIO[Date])
var a = ADDCOLUMNS(all(TURNOVER),"Dem",COALESCE([DATA_DEMISSÃO],TODAY()))
var b = ADDCOLUMNS(a,"i",COUNTROWS(INTERSECT(d,CALENDAR([DATA_ADMISSÃO],[Dem]))))
return countrows(filter(b,[i]>0))

[LeonardCs]

You understood perfectly. I conducted some tests, and it worked very well. Thank you so much for your help once again 🙌🙌