Forum Discussion
Nearest date to slicer selection
Hi,
How can I modify this DAX to find the nearest date (can be before or after) to the minimum value the user selects on the date range slicer? This data set doesn't have every possible day in it, so if someone doesn't pick one of the exact dates, it will return a zero but I want it to return the count of employees based on the nearest date to what the user puts in the slicer. Thanks!
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.
3 Replies
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.
