Forum Discussion

admera's avatar
admera
Frequent Visitor
2 years ago
Solved

Stock reduction based on demand (2)

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 w...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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] )
)

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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] )
)