Forum Discussion

Coffee321's avatar
Coffee321
Frequent Visitor
2 years ago
Solved

Lookup value from another table based on key match and nearest date match

I'm trying to bring a value from another table but there is no relationship because of duplicates. This is the Dim table, the value I'm trying to bring over is on the PerUnit column: ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Coffee321 

    you can create a column

     

    Column =
    var _last=maxx(FILTER(Dim,'Dim'[Date]<='Fact'[Date]&&'Fact'[Item]=Dim[Item]),'Dim'[Date])
    return maxx(FILTER(Dim,'Dim'[Item]='Fact'[Item]&&'Dim'[Date]=_last),Dim[PerUnit])
     
     
    or create a measure
     
    Measure =
    var _last=maxx(FILTER(all(Dim),'Dim'[Date]<=max('Fact'[Date])&&Dim[Item]=max('Fact'[Item])),'Dim'[Date])
    return maxx(FILTER(all(Dim),Dim[Item]=max('Fact'[Item])&&'Dim'[Date]=_last),'Dim'[PerUnit])
     
    pls see the attachment below
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

Coffee321 

you can create a column

 

Column =
var _last=maxx(FILTER(Dim,'Dim'[Date]<='Fact'[Date]&&'Fact'[Item]=Dim[Item]),'Dim'[Date])
return maxx(FILTER(Dim,'Dim'[Item]='Fact'[Item]&&'Dim'[Date]=_last),Dim[PerUnit])
 
 
or create a measure
 
Measure =
var _last=maxx(FILTER(all(Dim),'Dim'[Date]<=max('Fact'[Date])&&Dim[Item]=max('Fact'[Item])),'Dim'[Date])
return maxx(FILTER(all(Dim),Dim[Item]=max('Fact'[Item])&&'Dim'[Date]=_last),'Dim'[PerUnit])
 
pls see the attachment below