Stock reduction based on demand (2)

[admera]

Dear All Mastah

Hii everyone I don't have any idea for this measure about inventory, please help. I want to get the results from reducing stock to demand. The point is that the stock calculation will decrease if there is demand on each date. please check table my expectations

I have 2 table fact. table demand & table stock.

Table Demand

ArticleCode	Date Demand	Demand
A001	01-Mar	200
A001	02-Mar	300
A001	03-Mar	200
A001	04-Mar	100
A002	01-Mar	200
A002	02-Mar	300
A002	03-Mar	4,400
A002	04-Mar	11
A002	05-Mar	131
A002	06-Mar	131
A002	07-Mar	313
A002	08-Mar	131
A003	03-Mar	200
A003	04-Mar	100
A003	05-Mar	200
A003	06-Mar	300
A003	07-Mar	1,000
A003	08-Mar	900

Table Stock

ArticleCode	Stock
A001	10,000
A002	9,000
A003	5,000

My Expectation Matrix

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Balance: = 
VAR _demandcumulate =
    CALCULATE (
        SUM ( Demand[Demand] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( Date_demand[Date Demand] ),
            ORDERBY ( Date_demand[Date Demand], ASC )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ),
        MAXX ( Stock, Stock[Stock] ) - _demandcumulate
    )

Stock: = 
VAR _demandcumulatepreviousdate =
    CALCULATE (
        SUM ( Demand[Demand] ),
        WINDOW (
            1,
            ABS,
            -1,
            REL,
            ALL ( Date_demand[Date Demand] ),
            ORDERBY ( Date_demand[Date Demand], ASC )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ),
        MAXX ( Stock, Stock[Stock] ) - _demandcumulatepreviousdate
    )

Demand: = 
IF (
    HASONEVALUE ( Stock[ArticleCode] ) && HASONEVALUE ( Date_demand[Date Demand] ),
    SUM ( Demand[Demand] )
)

[admera]

Hii Kim

I have problem with this measure when I have hierarchy under Article Code, lets say Article Code Detail. if I breakdown by hierarchy Article Code Detail its not correct. can you help me to re-write dax Kim? thanks before

sample
A001
 A001-1

 A001-2

 A001-3

A002
 A002-1

 A002-2

 A002-3

[Jihwan_Kim]

Hi,

I am not 100% sure if I understood your question correctly, but if you are adding hierarchy, I think you also need to re-define stock data.

Please provide sample data model with re-defined information, and then I can try to look into it.

Thank you.