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

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