The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
I'd recommend either modifying your data source directly or using Power query to replace the null values with a "0" where applicable
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