Display all the records in the table visual between the selection of Start Date and End Date

[Anonymous]

EmpID	StartDate	EndDate
1	01/01/2024	05/01/2024
2	02/01/2024	06/01/2024
3	03/01/2024	07/01/2024
4	04/01/2024	05/02/2024
5	05/01/2024	06/02/2024
6	06/01/2024	07/02/2024
7	07/01/2024	08/02/2024
8	08/01/2024	12/12/2025
9	09/01/2024	01/01/2025
10	10/01/2024	10/01/2024

I have the Employee Table, 

I want to display all the records in the table visual between the selection of Start Date and End Date like the following :

@AmiraBedh 

[Anonymous · Accepted solution]

Hi  @Anonymous ,

You need to create two slicer tables that have no relationship to the original table so that the slicers are not automatically filtered when they are used.

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[StartDate])

Table 3 =
DISTINCT('Table'[EndDate])

2. Create measure.

Flag=
var _selectstart=SELECTEDVALUE('Table 2'[StartDate])
var _selectend=SELECTEDVALUE('Table 3'[EndDate])
return
IF(
    OR(MAX('Table'[StartDate])>=_selectstart&&MAX('Table'[StartDate])<=_selectend,
     MAX('Table'[EndDate])>=_selectstart&&MAX('Table'[EndDate])<=_selectend)
    ,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Try a measure like

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>= Min('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Current Employees =
var _min = Minx(allselected(Employee), Employee[Start Date])
Var _max = Maxx(allselected(Employee), Employee[Start Date])
return
CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=__max && (ISBLANK(Employee[End Date]) || Employee[End Date]>= _min))),(Employee[Employee Id ]))

If date is connected with any date use cross filter like first measure, if not you can avoid that like second measure

You can also refer

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[Anonymous]

I am not calculating the number I am just displaying.

If I select a date from StartDate and a date from EndDate, the table visual will display record in the interval of the selection.