Showing only VALID data points when choosing a date (date picker)

[Janica123]

Hello everyone, 

My data looks like this: 

I need to create a bar chart that shows the number of employees in each team based on a selected date (slicer).

This means I need a logic that takes the selected date from the date slicer and checks it against the columns Valid from and Valid until in the dataset to determine which value is valid. The result should only display the valid values (the team) for each employee.

Example: The date-picker is set to 02.04.2024.

Result:
Team A = 1 employee,
Team B = 1 employee,
Team C = 0 employees.

For the slicer, I need a date picker (with a calendar display). Is it possible to use the "Before or After" date slicer option and then simply remove the slider and cover the grayed-out date boxes with a white shape?

Can anyone please help me, I have no idea how to solve this!

I really appreciate your help!

Best regards

[bhanu_gautam · Accepted solution]

@Janica123 , Add a slicer to your report.
Set the slicer to use the Before or After option.
Customize the slicer to show a calendar display.

Create a measure that checks if the selected date falls within the Valid from and Valid until range for each employee.

SelectedDate = SELECTEDVALUE('DateTable'[Date])

EmployeesInTeam =
CALCULATE(
COUNTROWS('EmployeeTable'),
FILTER(
'EmployeeTable',
'EmployeeTable'[Valid from] <= [SelectedDate] &&
'EmployeeTable'[Valid until] >= [SelectedDate]
)
)

Add a bar chart to your report.
Use the Team column for the axis.
Use the EmployeesInTeam measure for the values

To remove the slider and cover the grayed-out date boxes, you can use a white shape to cover the unwanted parts of the slicer.

[mark_endicott · Accepted solution]

@Janica123 - if you have a date table or calendar for your slicer you can do this with a DAX measure structured like so:

CALCULATE (
    COUNTX (
        FILTER (
            Table,
            Table[Valid From] <= MAX ( 'Date'[Date] )
                && Table[Valid until] >= MIN ( 'Date'[Date] )
        ),
        ( Table[Emloyee ID] )
    )
)

As for your slicer issue, the settings have the option to remove the slider, and you can cover the boxes, but then users will not be able to change the dates. 

I would suggest allowing the users to select a range, but explaining to them what they can and cannot do. The DAX measure will provide an accurate count between the dates they select. 

If this helps, please accept as the solution to help others with the same challenge. 

[Ritaf1983 · Accepted solution]

Hi @Janica123 
To get the wanted result you can create a disconnected date table :

And use dax measure like :

valid employees =

var min_date = min('calendar'[Date])

var max_date = max('calendar'[Date])

RETURN

CALCULATE(DISTINCTCOUNT('Table'[Employee id]),

FILTER('Table',

min('Table'[Valid from]) >= min_date && max('Table'[Valid from]) <= max_date && max('Table'[Valid untill])> max_date))

Note:
If you're creating a calendar using DAX, it's highly recommended to change the "Valid Until" date for those who are still active to a dynamic date like "Today" or "the day after Today" to avoid generating unnecessary rows.

In any case, for display purposes here, I filtered the slicer up to Today

The pbix is attached

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

[Anonymous · Accepted solution]

Hi @Janica123 

Thanks for the reply from bhanu_gautam , mark_endicott and Ritaf1983 .

Please refer to the following test.

1. Create a calculated table as "date slicer"

Date = CALENDAR(DATE(2024, 1, 1), DATE(2024, 5, 31))

2. Create a measure as follows

Measure = 
VAR _selectedDate = MIN('Date'[Date])
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]), FILTER('Table', _selectedDate >= 'Table'[Valid From] && _selectedDate <= 'Table'[Valid until])) + 0
RETURN
_count

3. Use the "After" date slicer option. Then turn off the slider. Use a white shape to cover the grey option.

You can also hold down "Shift" while selecting the slicer and shape, then right-click to "Group" them so they can be moved together.

Output:

Best Regards,
Yulia Xu

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

[Anonymous · Accepted solution]

Hi @Janica123 

Thanks for the reply from bhanu_gautam , mark_endicott and Ritaf1983 .

Please refer to the following test.

1. Create a calculated table as "date slicer"

Date = CALENDAR(DATE(2024, 1, 1), DATE(2024, 5, 31))

2. Create a measure as follows

Measure = 
VAR _selectedDate = MIN('Date'[Date])
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]), FILTER('Table', _selectedDate >= 'Table'[Valid From] && _selectedDate <= 'Table'[Valid until])) + 0
RETURN
_count

3. Use the "After" date slicer option. Then turn off the slider. Use a white shape to cover the grey option.

You can also hold down "Shift" while selecting the slicer and shape, then right-click to "Group" them so they can be moved together.

Output:

Best Regards,
Yulia Xu

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

[Janica123]

Hi @Anonymous,

thanks for your help!

I've implemented your code without the +0 at the end and that worked, thank you very much.

The only problem that I have now is that I need to show a table with the employees IDs next to my column chart.

Assume the DAX formula counts 7 employees for a team named B in the selected time period. When I click on this bar, a nearby table should be filtered to display only the IDs of these 7 employees.

Currently, this cross-filtering does not work. When I click on the bar with 7 employees, it shows ALL IDs of employees who are or were part of this team. The validation of whether these IDs were valid in this team at the selected point in time is being ignored.

Do I need a new DAX formula for the table that also checks, based on the time period and the columns Valid from and Valid until, which records are valid?

[Anonymous · Accepted solution]

Hi @Janica123 

If I understand correctly, you want the following output. 

If you don’t have a +0 in the measure, then it should be blank. You can set the measure as follows: first select the table visualization, then put the measure into the visual-level filters.

Hope this helps.

Best Regards,
Yulia Xu

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

[Ritaf1983 · Accepted solution]

Hi @Janica123 
To get the wanted result you can create a disconnected date table :

And use dax measure like :

valid employees =

var min_date = min('calendar'[Date])

var max_date = max('calendar'[Date])

RETURN

CALCULATE(DISTINCTCOUNT('Table'[Employee id]),

FILTER('Table',

min('Table'[Valid from]) >= min_date && max('Table'[Valid from]) <= max_date && max('Table'[Valid untill])> max_date))

Note:
If you're creating a calendar using DAX, it's highly recommended to change the "Valid Until" date for those who are still active to a dynamic date like "Today" or "the day after Today" to avoid generating unnecessary rows.

In any case, for display purposes here, I filtered the slicer up to Today

The pbix is attached

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

[mark_endicott · Accepted solution]

@Janica123 - if you have a date table or calendar for your slicer you can do this with a DAX measure structured like so:

CALCULATE (
    COUNTX (
        FILTER (
            Table,
            Table[Valid From] <= MAX ( 'Date'[Date] )
                && Table[Valid until] >= MIN ( 'Date'[Date] )
        ),
        ( Table[Emloyee ID] )
    )
)

As for your slicer issue, the settings have the option to remove the slider, and you can cover the boxes, but then users will not be able to change the dates. 

I would suggest allowing the users to select a range, but explaining to them what they can and cannot do. The DAX measure will provide an accurate count between the dates they select. 

If this helps, please accept as the solution to help others with the same challenge. 

[bhanu_gautam · Accepted solution]

@Janica123 , Add a slicer to your report.
Set the slicer to use the Before or After option.
Customize the slicer to show a calendar display.

Create a measure that checks if the selected date falls within the Valid from and Valid until range for each employee.

SelectedDate = SELECTEDVALUE('DateTable'[Date])

EmployeesInTeam =
CALCULATE(
COUNTROWS('EmployeeTable'),
FILTER(
'EmployeeTable',
'EmployeeTable'[Valid from] <= [SelectedDate] &&
'EmployeeTable'[Valid until] >= [SelectedDate]
)
)

Add a bar chart to your report.
Use the Team column for the axis.
Use the EmployeesInTeam measure for the values

To remove the slider and cover the grayed-out date boxes, you can use a white shape to cover the unwanted parts of the slicer.