Forum Discussion

admera's avatar
admera
Frequent Visitor
2 years ago
Solved

Stock reduction based on demand

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

 

Table Demand

ArticleCodeDate DemandDemand
A00101-Mar200
A00102-Mar300
A00103-Mar200
A00104-Mar100
A00201-Mar 200 
A00202-Mar 300 
A00203-Mar 4,400 
A00204-Mar 11 
A00205-Mar 131 
A00206-Mar 131 
A00207-Mar 313 
A00208-Mar 131 
A00303-Mar200
A00304-Mar100
A00305-Mar 200 
A00306-Mar 300 
A00307-Mar 1,000 
A00308-Mar 900 

 

Table Stock

ArticleCodeStock
A001 10,000 
A002 9,000 
A003 5,000 

 

My Expectation Matrix



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,admera

    Regarding the issue you raised, my solution is as follows:

    1. First, the stock value is referenced into the main table using the calculated column:

     

    INTIStock = LOOKUPVALUE('Table Stock'[Stock],'Table Stock'[ArticleCode],'Table Demand'[ArticleCode])

     

    2.Then depending on your needs, I create the following calculated columns individually:

     

    Balance = 
var _sum=
SUMX(
    FILTER(ALL('Table Demand'),
    'Table Demand'[Date Demand]<=EARLIER('Table Demand'[Date Demand])&&'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])),[Demand])
return
[INTIStock] - _sum

    Stock = 
var _date=
MAXX(FILTER(ALL('Table Demand'),
'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])&&'Table Demand'[Date Demand]<EARLIER('Table Demand'[Date Demand])),[Date Demand])
var _value=
SUMX(
    FILTER(ALL('Table Demand'),
    'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])&&'Table Demand'[Date Demand]=_date),[Balance])
return
IF(
    _value<>BLANK(),_value,[INTIStock])

     

    3.Here's my final result, which I hope meets your requirements.

    Please find the attached pbix relevant to the case.

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,admera

    Regarding the issue you raised, my solution is as follows:

    1. First, the stock value is referenced into the main table using the calculated column:

     

    INTIStock = LOOKUPVALUE('Table Stock'[Stock],'Table Stock'[ArticleCode],'Table Demand'[ArticleCode])

     

    2.Then depending on your needs, I create the following calculated columns individually:

     

    Balance = 
var _sum=
SUMX(
    FILTER(ALL('Table Demand'),
    'Table Demand'[Date Demand]<=EARLIER('Table Demand'[Date Demand])&&'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])),[Demand])
return
[INTIStock] - _sum

    Stock = 
var _date=
MAXX(FILTER(ALL('Table Demand'),
'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])&&'Table Demand'[Date Demand]<EARLIER('Table Demand'[Date Demand])),[Date Demand])
var _value=
SUMX(
    FILTER(ALL('Table Demand'),
    'Table Demand'[ArticleCode]=EARLIER('Table Demand'[ArticleCode])&&'Table Demand'[Date Demand]=_date),[Balance])
return
IF(
    _value<>BLANK(),_value,[INTIStock])

     

    3.Here's my final result, which I hope meets your requirements.

    Please find the attached pbix relevant to the case.

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.