Nearest date to slicer selection

[rel2022]

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!

MinValue =

VAR Min1 = CALCULATE( COUNT('Sheet1'[Employee ID]), FILTER('Sheet1', 'Sheet1'[Date Effective] = MIN('Dates'[Date]) ))

return

IF(ISBLANK(Min1), 0, Min1)

[v-binbinyu-msft · Accepted solution]

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.

[rel2022]

Hi @v-binbinyu-msft 

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!!

[v-binbinyu-msft]

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.