Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Fill in blank with last non blank value in matrix

Hi there,

 

I have a huge database of Inventory Management having More than one stores as well as products, 

everyday stock is updating by some value(UpdateQty) and having one min value(MinQty)

I calculate values like - Out-of-Stock, Under-Stock, In-Stock using calculation (UpdateQty and MinQty),

Finally, I create a calculated column as StockCheck, which shows the status of every product.

 

Now I have to show all details in Matrix Chart(Row- Product, Column-Dates, Values-StockCheck),

but it shows blank values if stock is not updated on that date.

 

So my problem is that I have to calculate the last value on that blank values,

Stores/Dates   1      2       3       4       5      6       7       8        9      10       11       12      13      14       15 

Store3/A      Out                                               In                                               under

Store3/B                                 under                                             In

Store4/A       In                                                 Under                                                 Out

Store4/B       In

Store5/A      Out                                                In

 

 

I use the following Measure but it now shows the correct value:

Fillvalue1 =
         var _currdate = MAX(ItemInventoryHistory[LastUpdated])
         var _lastValue = CALCULATE(LASTNONBLANK(ItemInventoryHistory[LastUpdated],ItemInventoryHistory[LastUpdated]), FILTER(ALL(ItemInventoryHistory),ItemInventoryHistory[LastUpdated] <= MAX(ItemInventoryHistory[LastUpdated])))

return
IF(MAX(ItemInventoryHistory[Stock Check])=BLANK(),_lastValue,MAX(ItemInventoryHistory[Stock Check]))
 
 
Plz, Help.

 

2 REPLIES 2
amitchandak
Super User
Super User

Can you share some sample data. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi Amit,

 

Thank you for showing interest to solve my issue.

 

Please find the link below to access the .pbix file

 

https://community.powerbi.com/t5/Desktop/Previous-Last-Non-Blank-Month-s-Value/m-p/918713/highlight/...

 

Here I have calculated all fields and show the expected output,

if blank the show previous status.

 

Thank You

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.