topic Re: CALCULATE REMAINING INVENTORY in DAX Commands and Tips

CALCULATE REMAINING INVENTORY

Anonymous — Tue, 07 Feb 2023 15:51:15 GMT

Hello, i have a stock value by product at the begining of the month , and i also have a demand table where i have the forecast units
this two tables are related to a date table and a product table

what i need to do is to sustract the demand for the last stock value like in this example :

i need to calculate in dax the remaining stock Column

https://drive.google.com/drive/folders/1Pn0xT_N6BTDfqmRHdOFQcfa0_mJWPv8s?usp=sharing

MONTH STOCK DEMAND REMAININING STOCK
01/01/2022 1000 100 900
01/02/2022 200 700
01/03/2022 300 400
01/04/2022 100 300
01/05/2022 200 100
01/06/2022 100 0
01/07/2022 100 0
01/08/2022 200 0
01/09/2022 200 0
01/10/2022 200 0
01/11/2022 30 0
01/12/2022 100 0

Re: CALCULATE REMAINING INVENTORY

ppm1 — Tue, 07 Feb 2023 00:12:56 GMT

Here is a measure expression that seems to work. Replace T4 with your actual table name.

Remaining = VAR maxdate = MAX ( T4[MONTH] ) VAR result = CALCULATE ( SUMX ( t4, T4[STOCK] - T4[DEMAND] ), REMOVEFILTERS ( T4[MONTH] ), T4[MONTH] <= maxdate ) RETURN IF ( result > 0, result, 0 )

Pat

Re: CALCULATE REMAINING INVENTORY

Anonymous — Tue, 07 Feb 2023 15:48:04 GMT

this works great , i thought i could figured out with that simple example , but i cant addapt it to mi example , i attach a pbix to show mi particular case , because the data its stored in different tables like

stock : its the initial stock snapshot at the beginning of the month by country and product

demand :its the yearly demand by country , product and month

country: its the dim country

product :its the dim product

transit : its the future income of products , it should be add to the stock on a specific date

calendar table : calendar dim