Forum Discussion
Pull one value from other table based on date range
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
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 regardsBas
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 Geraldesryan_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?
