cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

CALCULATE REMAINING INVENTORY

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

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

2 REPLIES 2
Solution Sage

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

Microsoft Employee
Frequent Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors