Opening Stock For Multiple Products

[hammerman]

Hi Power BIers,

I've got a stock adjustment table that I'd like to use to calculate opening stock level. It's across multiple products and dates. 

I'd essentially like to fill down the opening balance by date until a new opening balance is entered. But then I'd also like to sum across products. Here's what I'm after. 

Here's my data table in Excel: 

And here's what I'd like the output to look like in Power BI: 

Note that I have a date table and a product control table. The formula needs to use these 2 tables to calculate the final result through this table, the stock adjustment table. 

I hope that makes sense. Thanks for your help!

[hammerman]

Thanks @amitchandak although unfortunately that hasn't worked. I'd like it to sum up the total across each product for each date from the inventory table at the total level. This formula just gives me the last non blank.

I've got a date table and a product control table in my model. I'm thinking it might be something like for each date SUMX the last non blank?  

[v-janeyg-msft · Accepted solution]

Hi, @hammerman 

You can manually modify the value of total. Use SELECTEDVALUE ( table[column]1 ) = BLANK ().

Like this:

measure =
IF (
    SELECTEDVALUE ( Date[Date] ) = BLANK (),
    CALCULATE ( SUM ( Table[Inventory Value] ) ),//calculate total sum
    CALCULATE (
        LASTNONBLANKVALUE ( Date[Date], SUM ( Table[Inventory Value] ) ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@hammerman , Prefer to use a Date table, this will ensure all works onlt for date

calculate(lastnonblankvalue(Date[Date], Sum(Table[Inventory Value])), filter(all('Date'), 'Date'[Date] <= max( 'Date'[Date] )))