I have two tables. how to lookup value in sequence .

[powerbighy]

Dear all ，

I have two tables.

The first one is for demand qty with a request date

The second one is for available items.

I want the result just like the values in the third table.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column of Table2 to the Date column of the Calendar Table.  Create a single column Item table with a relationship (Many to One and Single) from the Item column of both tables to the single column Item Table.  To your visual, drag Date from the Calendar Table and Item from the single column Item table.  Drag SN from Table2.  Write this measure

Measure = countrows(Table2)

Hope this helps.

[powerbighy]

Hi Ashish ,

Thanks a lot for your reply and solution.

Though,May i ask what is the Measure= countrows(Table2) for?

and for this "Create a Calendar Table with a relationship (Many to One and Single) from the Date column of Table2"      There is no date value in table 2.

Could you please provide more details?

THanks.

[Ashish_Mathur]

You are welcome.  That will allow you to see the relevant SN's for each id.  I meant Table1

[powerbighy]

Dear Ashish

Thanks a lot.

I model the data according to your method ，though I cannot get the right result. 

I have no idea if I understand you fully.

Could you please help to double-check？

[Ashish_Mathur]

Hi,

I cannot help without looking at your PBI file.  Share the download link of your PBI file and show the expected result.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523