Add a column to a Data table from another lookup table

[amaleranda]

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

[vivran22 · Accepted solution]

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

[amaleranda]

@vivran22  works a magic. thanks mate

[amitchandak]

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