Forum Discussion

amaleranda's avatar
amaleranda
Icon for Post Patron rankPost Patron
6 years ago
Solved

Add a column to a Data table from another lookup table

Hello Gurus,

 

I have a lookup tabel like below(Image1). I need to add a new column (Revision Number) to my continues data tabel(Image2) based on below condition. my date tabel has only one column at the moment it is just the date as below(Image2).

 

Say  all the dates between 29/12/2018 tp 18/01/2019 Revision Number column on the data tabel would be 18W52-03

       all the dates between 19/01/2019 tp 08/02/2019 Revision Number column on the data tabel would be 19W03-06 etc

Image 1

Image1

Image 2

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • vivran22's avatar
    vivran22
    6 years ago

    Hello amaleranda 

     

    Try this:

    RevisionNumber = 
CALCULATE(
       VALUES(Image1[Revision Number]),
FILTER(
Image1,
Image2[Date]>= Image1[Revsion_Start_Date]
&&
Image2[Date] <= Image1[Revsion_End_Date]
)
)

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

     

3 Replies

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello amaleranda 

     

    Try this:

    RevisionNumber = 
CALCULATE(
       VALUES(Image1[Revision Number]),
FILTER(
Image1,
Image2[Date]>= Image1[Revsion_Start_Date]
&&
Image2[Date] <= Image1[Revsion_End_Date]
)
)

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try to have a new column like

    revision no = maxx(filter(table1, table1[start_date]<=table2[Date] &&table1[end date]>=table2[Date]), table1[Revision Number])