Lookup returning blanks

[tangytang]

 Hello PBI,

Ive been trying to resolve my lookup issue by following this article.

My objective is to display the RemainingQuantity from the previous day as the OpeningQuantity for this day.

First, I ranked the dates with:

Rank = RANKX(ConsolidatedQuery_v3,ConsolidatedQuery_v3[Date],,ASC)

Then, I performed a lookup where:

OpeningQuantity = LOOKUPVALUE(ConsolidatedQuery_v3[RemainingQuantity],ConsolidatedQuery_v3[Rank],ConsolidatedQuery_v3[Rank]-1)

Without the OpeningQuantity, the data looks like so:

However, once i add the "Opening Data", the table turns blank:

I suspect this may due to the fact that I have multiple Branches. For example, Branch "BJ" might have quantities recorded everyday between 01/01/16 to 01/01/17.

Similarly, Branch "VC" might have quantities recorded everyday between 01/01/16 to 01/01/17. Therefore a particularly date like 03/03/16 can appear twice.

But I am not sure. If anyone could help me, that'll be great! 🙂

[v-yuta-msft · Accepted solution]

Hi tangytang,

[Rank] and [OpeningQuantity] are both calculate columns, right? I have tested on your issue but they work well on my side, see screenshot below:

 

Please refer to PBIX file here: https://www.dropbox.com/s/b9mgv7q255zicvu/Lookup%20returning%20blanks.pbix?dl=0.

Regards,

Jimmy Tao