Forum Discussion

NJ81858's avatar
NJ81858
Helper 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:

DateClosest Holiday
7/6/2022Independence Day
11/21/2022Thanksgiving

 

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

  • 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

3 Replies

  • johnt75's avatar
    johnt75
    Super 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
      Helper 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.

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    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