Closest Date

[NJ81858]

I have a table of dates and another table of federal holidays and what the name of that is and I just need a way to find which holiday is the closest to a given day.

EX.

Table 1:

Date	Closest Holiday
7/6/2022	Independence Day
11/21/2022	Thanksgiving

So essentially I just need a way to populate the "Closest Holiday" column. Thanks in advance!!

[johnt75]

You can try

Closest Holiday =
VAR currentDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR closestHoliday =
    SELECTCOLUMNS (
        TOPN (
            1,
            'Holidays',
            ABS ( DATEDIFF ( currentDate, 'Holidays'[Date], DAY ) ), ASC,
            'Holidays'[Date], ASC
        ),
        "@val", 'Holidays'[Name]
    )
RETURN
    closestHoliday
 

This will pick the closest holiday in the past in the event of a tie. You could change it to 'Holidays'[Date], DESC if you wanted the closest holiday in the future instead.

[NJ81858]

That filled in a holiday for me, unfortunately it filled in the same holiday for each individual date value no matter what.

[SpartaBI · Accepted solution]

@NJ81858 Add this calc column to your date table:

Closest Holiday = 
VAR _currentDate = 'Date'[Date]
VAR _tbl =
    ADDCOLUMNS(
        Table1,
        "@Diff", ABS(_currentDate - Table1[Date])
    )
VAR _closeset = MINX(_tbl, [@Diff])
VAR _filtered_rows = 
    FILTER(
        _tbl,
        [@Diff] = _closeset
    )
VAR _ties = TOPN(1, _filtered_rows, Table1[Date], ASC)
VAR _closest_date = CONCATENATEX(_ties, Table1[Date], " ,")
RETURN
    _closest_date

      

Showcase Report – Contoso By SpartaBI