Forum Discussion

basrooz's avatar
basrooz
Icon for Advocate I rankAdvocate I
4 years ago
Solved

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 va...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    basrooz , Try a new column like

     

    new column =
    var _max = maxx(filter(User, User[USer ID] = Order[User ID] && User[Date] <= Order[Order Date]),User[Date])
    return
    maxx(filter(User, User[USer ID] = Order[User ID] && User[Date] =_max),User[Cost per order])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

basrooz , Try a new column like

 

new column =
var _max = maxx(filter(User, User[USer ID] = Order[User ID] && User[Date] <= Order[Order Date]),User[Date])
return
maxx(filter(User, User[USer ID] = Order[User ID] && User[Date] =_max),User[Cost per order])