Approximate Match for Dates in DAX

[Anonymous]

Hi All,

I have two tables:

1. List of every day of the year
2. List of (Column A) Record Dates that are taken on working days, and (Column B) a list of values.

I need to do an appoximate match that returns Column B in Table 2, into Table 1. The issue is that a lookupvalue will not work as there is not an exact match for the weekend dates in table 1. In the case that the date is a weekend, i need to return the value for the following working day (i.e. the next value in column B in table 2).

Example is as follows:
Table 1                                                             Table 2                                                            Desired Outcome

Table 1Table 2Desired Outcome

Desired Outcome:  (Achieved with Appoximate Index Match in Excel)

Thank you!

[v-alq-msft]

Hi, @Anonymous 

Based on my research, you may create a measure as follows.

Value = 
var _currentdate = MAX('Table 1'[Date])
var _weekno = WEEKDAY(_currentdate)

return
IF(
    _weekno in {2,3,4,5,6},
    LOOKUPVALUE('Table 2'[ColumnB],'Table 2'[Date],_currentdate),
    IF(
        _weekno in {1,7},
        LOOKUPVALUE('Table 2'[ColumnB],'Table 2'[Date],
                    CALCULATE(
                          MIN('Table 1'[Date]),
                          FILTER(
                              ALL('Table 1'),
                              'Table 1'[Date]>_currentdate&&
                              WEEKDAY('Table 1'[Date]) = 2
                          )
                    )
        ),
        BLANK()
    )
)

Then you can put it in the table visual and here is the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I would create a 2nd column in table 1 that contains the "Monday" date for Saturday and Sunday entries.  Then link Table 1 and Table 2 based on this new column.  You can hide this new column to remove it from the report if required.

Depending on how your data is stored (i.e. as a date). You can get Day of the week out of the date, and then either add 1 or 2 to the result depending on whether its Saturday or Sunday.