next day lookupvalue in the same table

Question

Hi.&nbsp;I have a DailyTableId with all the ProductId which are active in my ecommerce each day. For instance:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;ProductId01/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;02/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;04/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;04/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;DailyTableId was created from Calendar and ProductId which contains ProductId, ActiveFrom and LastDayActive.&nbsp;I am trying to create a new column to find out if every productId was active the day after. So the desire result would be:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;ProductId&nbsp; &nbsp; | Previous Day Active01/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;101/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;101/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 002/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 103/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 003/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90r&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 004/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 004/01/2020&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;1 means that the ProductId is Active on next day and 0 means that it is the last day which the product is Active.&nbsp;May anyone help me please?&nbsp;Thanks

amitchandak · Accepted Answer

aramirez2 , if, it is continous date , new column like
isblank(countx(filter(Table,Table[Date] =earlier(Table[Date])+1 &amp;&amp; Table[ProductId] =earlier(Table[ProductId])),Table[Date]),0,1)
&nbsp;
noncontinous
&nbsp;
new column =var _max =minx(filter(Table,Table[Date] &gt;earlier(Table[Date]) &amp;&amp; Table[ProductId] =earlier(Table[Date]))return isblank(countx(filter(Table,Table[Date] =_max &amp;&amp; Table[ProductId] =earlier(Table[ProductId])),Table[Date]),0,1) //ProductId
&nbsp;
For a noncontinuous date. refer this for both column and measure
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

Forum Discussion

next day lookupvalue in the same table

2 Replies

Related content

lookupvalue

Using LookUpValue

lookupvalue PBI

LookupValue with Filter

lookupValue in the same table