Pull one value from other table based on date range

[basrooz]

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

User ID	Date	Cost per order
A	1-1-2019	100
A	1-1-2020	200
A	1-1-2021	300
B	1-1-2019	150
B	1-1-2020	180

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

ORDERS TABLE

User ID	Order date
A	5-5-2019
A	5-5-2020
A	5-5-2021
B	5-5-2019
B	5-5-2020
B	5-5-2021

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

User ID	Order date	Cost per order
A	5-5-2019	100
A	5-5-2020	200
A	5-5-2021	300
B	5-5-2019	150
B	5-5-2020	180
B	5-5-2021	180

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

CALCULATE(
    SUM(User[Cost per order]),
    ALL(Orders),
    FILTER(User[User ID]=Orders[User ID])
    )

Could you please tell me how to proceed?

[ryan_mayu]

@basrooz 

you can create a column

Column = 
VAR _date=maxx(FILTER('User','Order'[User ID]='User'[User ID]&&'User'[Date]<='Order'[Order date]),'User'[Date])
return maxx(FILTER(User,'Order'[User ID]='User'[User ID]&&'User'[Date]=_date),'User'[Cost per order])

[goncalogeraldes]

Hello there @basrooz ! I think you are almost there! Try this:

Cost per order =
var _last_date=
    CALCULATE (
        MAX ( User[Date] ),
        NOT ISBLANK ( User[Cost per Order] )
    )
var _last_user =
    CALCULATE (
        MAX ( User[User ID] ),
        User[Date] = LastDay,
        NOT ISBLANK ( User[Cost per Order] )
    )
return
CALCULATE ( SUM ( User[Cost per Order]),
        ALL(Orders),
        FILTER(User[User ID] = _last_user && User[Date] = _last_date)
)

 Alternatively you can also try with an IF():

Cost per order =
var _last_date=
    CALCULATE (
        MAX ( User[Date] ),
        NOT ISBLANK ( User[Cost per Order] )
    )
var _last_user =
    CALCULATE (
        MAX ( User[User ID] ),
        User[Date] = LastDay,
        NOT ISBLANK ( User[Cost per Order] )
    )
var _selected = SELECTEDVALUE(User[Cost per Order])
return
IF( User[User ID] = _last_user && User[Date] = _last_date),
    _selected
)

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

[basrooz]

@goncalogeraldes  thank you for the quick response.

I think the solution you provided is only picking up the last available cost per order from the User table. What I want to do is that is the Orders table is showing an order on 2019-5-5 , that the cost per order of the value that was assigned on 2019-1-1 is picked up. Does that make sense?

Best regards

Bas

[goncalogeraldes]

Hello there @basrooz ! Depending on the type of relationship between your two table you can either use SELECTEDVALUE() or RELATED(). Try this:

Cost per order =
var _date = SELECTEDVALUE( User[Date] )
var _user = User[User ID]
var _selected = SELECTEDVALUE(User[Cost per Order])
return
IF( Orders[User ID] = _last_user && Orders[Date] = _last_date),
    _selected
)

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

[basrooz]

@ryan_mayu @goncalogeraldes  Thank you both very much for your help.

I've tried implementing both of your solutions but it did not work. I used some of the intelligence in the formulas you shared to enhance it to the following:

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_  )
                
    )
    

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 similar to one of the Dates in the User table. The two dates 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?