Slicer Filter Between Dates Measure to take first date and measure to take last date

[Anonymous]

I have an slicer with a between dates.

I have an employee table with Start Date and End Date (this could be blank when the employee is still working), it also has an area column and an ID for each employee

I want to have a table that will give me the following columns

Area / Employees at start of date / Employees that were dropped during that time / Employees that were hired during that time / Employees at the end of time

I'm trying to get the distinctcount ID with measures trying to use the min and max of the month date but can't make it work. I need help with each measure I should use for thoes 4 columns

[v-frfei-msft]

Hi @Anonymous ，

Hope this bolg can help you.

https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields

Btw, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[MartynRamsden]

Hi @Anonymous 

What column is driving the slicer? Is it coming from a date dimension table?

Best regards,

Martyn

[Anonymous]

Exactly is a date table not related to this table, also I try using an slicer coming from both the start and end date for the employee

[MartynRamsden · Accepted solution]

Hi @Anonymous 

Try the measures below (you'll obviously have to swap out the columns references for your actual column names):

Employees at start of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] < MinDate,
    OR ( EmployeeTable[EndDate] > MinDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result

Employees started in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[StartDate] >= MinDate,
            EmployeeTable[StartDate] <= MaxDate 
        )
    )
RETURN
    Result

Employees dropped in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[EndDate] >= MinDate,
            EmployeeTable[EndDate] <= MaxDate 
        )
    )
RETURN
    Result

Employees at end of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] <= MaxDate,
    OR ( EmployeeTable[EndDate] >= MaxDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result

Hope this helps!

Best regards,

Martyn

[Anonymous]

Thank you very much, worked perfectly