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:

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

3 Replies

  • 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.

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

     


    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.


    SpartaBI Logo

    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    Showcase Report – Contoso By SpartaBI