Value before the firstnonblankvalue

[jmcph]

Hi, i am trying to compute for the value before the firstnonblankvalue. I am computing for Beginning Inventory of a particular model,  my date looks like this: 

Date                       Particulars                       Item                            Qty

9/30/2020               InvEnd                              Item 1                          100

10/1/2020               InvEnd                              Item 1                          90

10/31/2020             InvEnd                              Item 1                          50

My filters based on Calendar date.

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)

Inv = FIRSTNONBLANKVALUE( 'Calendar'[Date] ,

Sumx( Inventory, if ( Inventory[Particulars] = "End", Inventory[Amount] , 0)

)

then i used Calculate to get the 9/30/2020 value by:

Beginnng Inv = 

Calculate( [Inv] , Dateadd( 'Calendar'[Date] , -1 , Day))

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?

I hope you can help me and i make my question clear! 

Cheers!  

[v-stephen-msft]

Hi @jmcph ,

Sorry, not very clear.

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.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jmcph , Try a new column like

new column =
calculate(lastnonblankvalue([Date], sum(Table[Qty])), filter(Table, [item] =earlier([item]) && [Date] <earlier([Date])))