Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |