Value before the firstnonblankvalue

Question

Hi, i am trying to compute for the value before the firstnonblankvalue. I am computing for Beginning Inventory of a particular model,&nbsp; my date looks like this:&nbsp;&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Particulars&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Item&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Qty9/30/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;InvEnd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Item 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10010/1/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;InvEnd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Item 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9010/31/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;InvEnd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Item 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp;My filters based on Calendar date.&nbsp;Since the ending inventory of a particular day will be the beginning inventory of the next day i didnt include a separate beginning inventory column. My current DAX on the beginning inventory looks like this ( for October Beginning Inventory)&nbsp;Inv = FIRSTNONBLANKVALUE( 'Calendar'[Date] , Sumx( Inventory, if ( Inventory[Particulars] = "End", Inventory[Amount] , 0))&nbsp;then i used Calculate to get the 9/30/2020 value by:&nbsp;Beginnng Inv =&nbsp;Calculate( [Inv] , Dateadd( 'Calendar'[Date] , -1 , Day))&nbsp;But makes me think, that my DAX wont work if there is no data encoded for 9/30/20. What if the last inventory was made 9/29/20?&nbsp;I hope you can help me and i make my question clear!&nbsp;&nbsp;Cheers!&nbsp;&nbsp;

amitchandak · Answer

jmcph , Try a new column like
new column = calculate(lastnonblankvalue([Date], sum(Table[Qty])), filter(Table, [item] =earlier([item]) &amp;&amp; [Date] &lt;earlier([Date])))

anonymous · Answer

Hi&nbsp;jmcph&nbsp;,&nbsp;Sorry, not very clear.&nbsp;Do you want to put the first non-blank value (100) in the Inventory table into the row of the day before the corresponding date in the calendar table?FIRSTNONBLANKVALUE has already filtered out non-blank values, so if the value on September 30 is blank, it will continue to find the first non-blank value.&nbsp;&nbsp;&nbsp;Best Regards,Stephen Tao&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Value before the firstnonblankvalue

2 Replies

Related content

Firstnonblankvalue in M

Funcionamiento incorrecto de FIRSTNONBLANKVALUE

Replace date values before specific date or error value to Null

What is the key difference between FIRSTNONBLANK and FIRSTNONBLANKVALUE ?

Filter out date values before year 2000