Forum Discussion

NewBIe2013's avatar
NewBIe2013
Regular Visitor
2 years ago
Solved

New opening inventory

Hi Bi folks, need your expertise here, I'm newbie to Power BI. Would like to have a power bi dax to be able to achieve below result, it's relatively simple in Excel.  The below shows a forecast of ...
  • 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

     

     

    Opening inventory: =
VAR _initialinventory = 500
VAR _start =
    CALCULATE ( MIN ( Data[Date] ), ALL ( Data[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        MAX ( Data[Date] ) = _start, _initialinventory,
        SUMX (
            WINDOW (
                1,
                ABS,
                -1,
                REL,
                SUMMARIZE ( ALL ( Data ), Data[Date], Data[Demand], Data[Stock receipt] ),
                ORDERBY ( Data[Date], ASC )
            ),
            Data[Stock receipt] - Data[Demand]
        ) + _initialinventory
    )

     

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

 

 

Opening inventory: =
VAR _initialinventory = 500
VAR _start =
    CALCULATE ( MIN ( Data[Date] ), ALL ( Data[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        MAX ( Data[Date] ) = _start, _initialinventory,
        SUMX (
            WINDOW (
                1,
                ABS,
                -1,
                REL,
                SUMMARIZE ( ALL ( Data ), Data[Date], Data[Demand], Data[Stock receipt] ),
                ORDERBY ( Data[Date], ASC )
            ),
            Data[Stock receipt] - Data[Demand]
        ) + _initialinventory
    )