Pull one value from other table based on date range

Hi,

I have two tables. One table contains a user ID, a date, and a cost per order.

The date is this table is showing as of when the cost per order should be applied. For instance for user A, the value of the user is 100 between 1-1-2019 and 1-1-2020. User A has a value assigned at 1-1-2021, user B did not get a value assigned. The value of user B after 1-1-2020 is 180, as the latest assigned value should always be taken into account.

USER TABLE

I have a second table showing showing when an order happened for a user.

ORDERS TABLE

I now would like to have an additional column in the second table, showing me the cost per order based on the user ID and the date. It should always pick up the last available cost. For instance, for user B there is no row showing the cost after 1-1-2021. It should then just pick up the value from the last available cost per order, which is 180.

ORDERS TABLE

I’ve created the calculation below, but I get stuck when I try to add a date filter.

VAR _orderdate_ = Orders[Order date]

VAR _nextdate_ =
    IF.EAGER(
        ISBLANK(
            CALCULATE(
                MINX( User , User[Date] ) ,
                FILTER( User , User[Date] > _orderdate_ )
            )
        )
        , DATE ( 2099 , 1 , 1 ) ,
            CALCULATE(
                MINX( User , User[Date] ) ,
                FILTER( User , User[Date] > _orderdate_ )
        ))

RETURN

    CALCULATE(
        SELECTEDVALUE(User[Cost per order]),
        ALL    ( User ) ,    
        FILTER ( User , User[User ID] = Orders[User ID] ) ,
        FILTER ( User , User[Date] >= _orderdate_ ) ,
        FILTER ( User , User[Date] < _nextdate_  )
                
    )

Explanation of the calculation:

I've added an _orderdate_  and a _nextdate_ variable. The _orderdate_ variable is simply picking the order date from the Orders column. The _nextdate_ variable is pulling the date in the Users table that comes next after the order date. If no date comes after the last order date, then the date is set to the year 2099.

In the actual calculation I'm then pulling the Cost per order for all Users where the order date is bigger or equal to the user date, and where the Cost per order date is less than the next date.

In theory this should now give me the Cost Per Order value for each value in the User table.

It doesn't work however, because something that has to do with the date filter. It somehow only returns a Cost Per Order when the Order Date in the Orders table is exactly similar to one of the Dates in the User table. For all other Order Dates it returns a blank value. The dates from the tables are not connected in the model. The Order Date is connected to a date table, but the Users table is standalone.

Would you happen to know why this function is only showing results for rows where the Order date is similar to date in the Users column?