Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
mordonez
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

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

2 REPLIES 2
ppm1
Solution Sage
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
mordonez
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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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