Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Janica123
Helper I
Helper I

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

Hello everyone, 

 

My data looks like this: 

Janica123_1-1736242246784.png

 

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

 

5 ACCEPTED SOLUTIONS
bhanu_gautam
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

mark_endicott
Super User
Super User

@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. 

View solution in original post

Ritaf1983
Super User
Super User

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

Ritaf1983_0-1736246159328.png

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

Ritaf1983_1-1736246532363.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

v-xuxinyi-msft
Community Support
Community Support

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.

vxuxinyimsft_1-1736315362334.png

 

vxuxinyimsft_0-1736315293307.png

 

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

vxuxinyimsft_3-1736315477900.png

 

Output:

vxuxinyimsft_4-1736315793416.png

 

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.

View solution in original post

Hi @Janica123 

 

If I understand correctly, you want the following output. 

vxuxinyimsft_1-1736841822359.png

 

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.

vxuxinyimsft_2-1736842431687.png

 

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.

View solution in original post

6 REPLIES 6
v-xuxinyi-msft
Community Support
Community Support

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.

vxuxinyimsft_1-1736315362334.png

 

vxuxinyimsft_0-1736315293307.png

 

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

vxuxinyimsft_3-1736315477900.png

 

Output:

vxuxinyimsft_4-1736315793416.png

 

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.

Hi @v-xuxinyi-msft,

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?

Hi @Janica123 

 

If I understand correctly, you want the following output. 

vxuxinyimsft_1-1736841822359.png

 

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.

vxuxinyimsft_2-1736842431687.png

 

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
Super User
Super User

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

Ritaf1983_0-1736246159328.png

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

Ritaf1983_1-1736246532363.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
mark_endicott
Super User
Super User

@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
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.