Forum Discussion
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
Previousday = SUMX(
calender,CALCULATE(
SUM('LBL'[Volume]),
'LBL'[Process] = "Stock",
PREVIOUSDAY(calender[Date])))
Any help would be great.
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
4 Replies
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.
Date Stock MAX 13/02/2013 10 14/02/2013 11 10 15/02/2013 8 11 16/02/2013 15 11 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
I'd recommend either modifying your data source directly or using Power query to replace the null values with a "0" where applicable
