Forum Discussion
Nearest date to slicer selection
Hi rel2022 ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps:
1. below is my test table
Table:
Dates:
Dates = CALENDAR ( FIRSTDATE ( 'Table'[Date Effective] ), LASTDATE ( 'Table'[Date Effective] ) )2. create a measure and add it to card visual, add a slicer visual with "Dates[Date]" column
Measure = VAR min_date = MIN ( Dates[Date] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Date Effective] >= min_date ) VAR ctn = COUNTROWS ( tmp ) RETURN IF ( ctn = 0, 0, ctn )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi rel2022 ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps:
1. below is my test table
Table:
Dates:
Dates =
CALENDAR (
FIRSTDATE ( 'Table'[Date Effective] ),
LASTDATE ( 'Table'[Date Effective] )
)
2. create a measure and add it to card visual, add a slicer visual with "Dates[Date]" column
Measure =
VAR min_date =
MIN ( Dates[Date] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Date Effective] >= min_date )
VAR ctn =
COUNTROWS ( tmp )
RETURN
IF ( ctn = 0, 0, ctn )
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thank you for your response! I should have been more clear.
My data table is grouped by pay period dates (every 2 weeks there is a group of employees - mostly the same employees but some join and some leave the organization), so I want the measure to count the number of employees in the pay period group closest to the 2 dates selected by the user in the slicer, and then I'll divide that number by 2 which will be my estimate of the number of employees working there during the time range inputted by the user.
An example data table would be:
| Employee ID | Pay Period Date |
| 1001 | 9/12/2021 |
| 1002 | 9/12/2021 |
| 1003 | 9/12/2021 |
| 1004 | 9/12/2021 |
| 1005 | 9/12/2021 |
| 1006 | 9/12/2021 |
| 1007 | 9/12/2021 |
| 1001 | 9/26/2021 |
| 1002 | 9/26/2021 |
| 1003 | 9/26/2021 |
| 1004 | 9/26/2021 |
| 1005 | 9/26/2021 |
| 1006 | 9/26/2021 |
| 1010 | 9/26/2021 |
| 1011 | 9/26/2021 |
| 1012 | 9/26/2021 |
In the above example table, if someone selects any date near or exactly 9/12/2021 (9/11/2021, 9/12/2021, 9/13/2021, 9/14/2021, etc.), the count would be based on the data from 9/12/2021 only and the result would be 7 employees. If someone selects a date near 9/26/2021, then the count would be based on the 9/26/2021 data only and the result would be 9 employees.
Thanks so much for any help you can provide!!
Hi rel2022 ,
Please adjust my measure according to your demands, the "nearest date" i cannot confirm, so please according youself need to adjust the measure.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.