Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have the following stock table
Date | Material | Batch | Stockvalue |
9/13/2023 | A | 2 | 100 |
9/12/2023 | A | 2 | 150 |
9/10/2023 | B | 1 | 75 |
9/9/2023 | A | 2 | 200 |
9/5/2023 | A | 1 | 5 |
A stock record will only be written if there is a transaction for that stock.
I need to get the total value of the stock.
Eg. if I check the stock value for 9/13/2023 for Material A the value should by 105 as Batch number 1 will have a value 5 and batch number 2 will have a value 100.
How do I get this done using Dax.
Thanks in advnce
Amal
Solved! Go to Solution.
Hi @amalsperera,
I've updated it a little bit. Please check if it works correctly - https://www.dropbox.com/scl/fi/d45372koa4xjr3qfylqo7/Sample.pbix?rlkey=nac40bon3nggko254pg36toee&dl=....
I assume you don't need Total. If you do, another update will be needed.
Hi @amalsperera,
Here's an option how to solve your problem:
And in plain text for convenience:
Measure =
VAR _maxDate = MAX ( [Date] )
VAR _tbl =
ADDCOLUMNS (
SUMMARIZE ( FILTER ( ALL ( Data ), [Date] <= _maxDate ), [Material], [Batch], "Latest Date", MAX ( [Date] ) ),
"Stockvalue",
VAR _currentMaterial = [Material]
VAR _currentBatch = [Batch]
VAR _currentDate = [Latest Date]
RETURN MINX ( FILTER ( ALL ( Data ), [Material] = _currentMaterial && [Batch] = _currentBatch && [Date] = _currentDate ), [Stockvalue] ) )
RETURN SUMX ( _tbl, [Stockvalue] )
Best Regards,
Alexander
Hi @barritown ,
Could you please attach the pbix. I am getting the total stock value as the value for all items.
Thanks
Amal
Sure, here it is
I think I forgot to mention an additional dimension table I used - you'll see in the file.
Best Regards,
Alexander
Hi @barritown ,
I tried it out, but it still is not giving per SKU. Could you please check the attached pbix and see what I am doing wrong?
https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=drive_link
Thanks
Amal
Hi @amalsperera,
Unfortunately, I cannot access the file you tried to share. Could you please double check sharing settings?
Best Regards,
Alexander
Hi @barritown,
Sorry I could not respond earlier. I was out of office. Could you please try now?
https://drive.google.com/file/d/17__K8x_EAdTUxeyinDHdw-WN5ggwbfxN/view?usp=sharing
Amal
Hi @amalsperera,
I've updated it a little bit. Please check if it works correctly - https://www.dropbox.com/scl/fi/d45372koa4xjr3qfylqo7/Sample.pbix?rlkey=nac40bon3nggko254pg36toee&dl=....
I assume you don't need Total. If you do, another update will be needed.
Hi @barritown ,
Thanks a bunch. I fiddled around with the measure and got the totals as well. This is the dax for it.
M_Distributor_Stock =
VAR _maxDate = MAX ( DistributorInventoryValue[Date] )
VAR _tbl =
ADDCOLUMNS (
SUMMARIZE ( FILTER(ALLEXCEPT(DistributorInventoryValue
,DistributorInventoryValue[MaterialNo])
, DistributorInventoryValue[Date] <= _maxDate )
, [MaterialNo]
, [BatchSerialNumber]
, "Latest Date", MAX ( DistributorInventoryValue[Date] ) ), "max Date" , _maxDate
, "Stock_value",
VAR _currentMaterial = [MaterialNo]
VAR _currentBatch = [BatchSerialNumber]
VAR _currentDate = [Latest Date]
Return CALCULATE(MINX(FILTER(DistributorInventoryValue
, [MaterialNo] = _currentMaterial
&& [BatchSerialNumber] = _currentBatch
&& [Date] = _currentDate)
,[StockValue])
,ALL('Date')))
RETURN SUMX ( _tbl, [Stock_value] )
I have accepted your post as the solution as you pointed me in the right direction. I have been struggling with this for some time.
Thanks again and Cheers,
Amal
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
22 | |
10 | |
10 | |
9 | |
7 |