How can i visualize old and new records of data in a power bi visual?

Question

i'm trying to put in place a dashboard for an hr departement.

The control database of employees has old and new records of rows after modifications. A row has a creation date and an expiration date, when a new employee is added he automatically has a "creation date" in the database and null for expiration date. if there's an update on the employee's data (for example he changed position from "intern" to "newly hired") then a new row is created with same employee but with the new updates and the creation date will take the date where the update occured, thus for the old record of the employee it will have an expiration date with a flag ="expired" .

i want to create a pie chart visual to represent the number of employees grouped by position (intern, new hires, managers etc..) and i want it to be sliced by date: if i choose date superior to the creation date the pie would display the employee as "newly hired" but if choose a date lesser than the creation date it would display it as "intern".

i'm new to power bi and i don't know how to implement this logic. i hope my explanation was clear! thank you ^^

i tried this dax measure but it's not displaying correctly the data:

MeasureTest =
VAR SelectedDate = SELECTEDVALUE('Date'[Date])
RETURN
    IF(
        SelectedDate >= SELECTEDVALUE(TABLE[DATECREATION]) && SelectedDate <= SELECTEDVALUE(TABLE[DATEEXPIRATION]),
        SELECTEDVALUE(TABLE[Position]),
        BLANK()
    )

Answer 1

The measure needs to count the number of active employees by their position for the date you select.

The logic here is that you need to filter the employee records to those active on the selected date and then count them by position.

EmployeeCountByPosition = 
VAR SelectedDate = MAX('Date'[Date]) 
RETURN
SUMX(
    VALUES(TABLE[Position]),
    CALCULATE(
        COUNTROWS(TABLE), 
        TABLE[DATECREATION] <= SelectedDate, 
        ISBLANK(TABLE[DATEEXPIRATION]) || TABLE[DATEEXPIRATION] > SelectedDate 
    )
)