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

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

 





      

Showcase Report – Contoso By SpartaBI