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

     


    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

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.