Forum Discussion

NJ81858's avatar
NJ81858
Icon for Helper IV rankHelper IV
4 years ago
Solved

Closest Date

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 Close...
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
NJ81858
Icon for Helper IV rankHelper IV
4 years ago

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