Forum Discussion
Dax to sum value grouped by two columns
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
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=0.
I assume you don't need Total. If you do, another update will be needed.
8 Replies
- barritown
Solution Sage
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
