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
AG32PD3
Regular Visitor

PreviousDay - input missing days or blank cell

I have an issue where I need to use previous day stock figure but due to data issues, I am missing days. Also if the stock position is 0 it won't shown in my input (example in table below). If finalized is greater than 0 and no row for stock it will be 0 on same day.

 

My linked calendar will pick up the missing days but my previousdays dax (below) will pull incorrect numbers if the scenario above happens.

 

Input Fields: Date, Area, Process (Stock or Finalized), Volume

 

AG32PD3_1-1740663883577.png

 

Previousday = SUMX(
calender,CALCULATE(
SUM('LBL'[Volume]),
'LBL'[Process] = "Stock",
PREVIOUSDAY(calender[Date])))

 

Any help would be great.

1 ACCEPTED SOLUTION

Hi @AG32PD3 ,

It looks like the issue is that the MAX function is picking up the highest stock value rather than the most recent available stock before the current row. Try using TOPN instead to retrieve the latest (previous) available stock value:

try DAX:

PreviousDayStock =
VAR PrevStock =
CALCULATE(
MAX('LBL'[Volume]),
'LBL'[Process] = "Stock",
'LBL'[Date] < EARLIER('LBL'[Date]), -- Ensure it's before the current date
ALLEXCEPT('LBL', 'LBL'[Area]), -- Consider only the same area
TOPN(1, 'LBL', 'LBL'[Date], DESC) -- Get the most recent stock before the current date
)
RETURN
IF(
'LBL'[Process] = "Finalized" &&
ISBLANK(LOOKUPVALUE('LBL'[Volume], 'LBL'[Date], 'LBL'[Date], 'LBL'[Process], "Stock")),
0, -- If finalized exists but no stock, set to 0
PrevStock -- Otherwise, get the last available stock
)


Explanation of Fix:
TOPN(1, 'LBL', 'LBL'[Date], DESC) ensures we get only the latest stock before the current row instead of the maximum value.
ALLEXCEPT('LBL', 'LBL'[Area]) ensures the calculation is performed within the same area.
The LOOKUPVALUE function ensures that if Finalized exists but Stock does not, it sets the value to 0.

 

Please mark this post as solution if it helps you. Appreciate Kudos

View solution in original post

4 REPLIES 4
Thaumaturgist
Helper I
Helper I

I'd recommend either modifying your data source directly or using Power query to replace the null values with a "0" where applicable

FarhanJeelani
Super User
Super User

Hi @AG32PD3 ,

You need a calculated column to get the previous available stock:

PreviousDayStock =
VAR PrevStock =
CALCULATE(
MAX('LBL'[Volume]),
'LBL'[Process] = "Stock",
'LBL'[Date] < EARLIER('LBL'[Date]),
ALLEXCEPT('LBL', 'LBL'[Area]) -- Ensures the stock is checked within the same area
)
RETURN
IF(
'LBL'[Process] = "Finalized" && ISBLANK(LOOKUPVALUE('LBL'[Volume], 'LBL'[Date], 'LBL'[Date], 'LBL'[Process], "Stock")),
0, -- If finalized exists but no stock, set to 0
PrevStock -- Otherwise, carry forward the last available stock
)

 

Please mark this post as solution if it helps you. Appreciate Kudos.

@FarhanJeelani - it nearly works but it’s seems to be taking the max value and not the previous value. I am new to Power BI so unsure of alternative. 

 

DateStockMAX
13/02/201310 
14/02/20131110
15/02/2013811
16/02/20131511

Hi @AG32PD3 ,

It looks like the issue is that the MAX function is picking up the highest stock value rather than the most recent available stock before the current row. Try using TOPN instead to retrieve the latest (previous) available stock value:

try DAX:

PreviousDayStock =
VAR PrevStock =
CALCULATE(
MAX('LBL'[Volume]),
'LBL'[Process] = "Stock",
'LBL'[Date] < EARLIER('LBL'[Date]), -- Ensure it's before the current date
ALLEXCEPT('LBL', 'LBL'[Area]), -- Consider only the same area
TOPN(1, 'LBL', 'LBL'[Date], DESC) -- Get the most recent stock before the current date
)
RETURN
IF(
'LBL'[Process] = "Finalized" &&
ISBLANK(LOOKUPVALUE('LBL'[Volume], 'LBL'[Date], 'LBL'[Date], 'LBL'[Process], "Stock")),
0, -- If finalized exists but no stock, set to 0
PrevStock -- Otherwise, get the last available stock
)


Explanation of Fix:
TOPN(1, 'LBL', 'LBL'[Date], DESC) ensures we get only the latest stock before the current row instead of the maximum value.
ALLEXCEPT('LBL', 'LBL'[Area]) ensures the calculation is performed within the same area.
The LOOKUPVALUE function ensures that if Finalized exists but Stock does not, it sets the value to 0.

 

Please mark this post as solution if it helps you. Appreciate Kudos

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.