PreviousDay - input missing days or blank cell

[AG32PD3]

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

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

Any help would be great.

[Thaumaturgist]

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

[FarhanJeelani]

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.

[AG32PD3]

@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. 

Date	Stock	MAX
13/02/2013	10
14/02/2013	11	10
15/02/2013	8	11
16/02/2013	15	11

[FarhanJeelani · 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