cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bklyn3
Advocate II
Advocate II

Looping and dynamically changing a table in DAX - The safety stock problem

Hello, 

 

Here is  a hard problem (at least for me) in DAX that I am able to solve in VBA (xls)  very easily with a loop. Unfortunetly loops are not very straighforward in DAX. 

 

I have parts leaving and arriving a warehouse.

Parts can only leave (be sold) if the warehouse  safety stock is at least 2 units. 

If the safety stock is not at least 2, the next part to leave will have to wait until a replacement part arrive.


At the beginning of the month there is an expected schedule for arrivals and departures of the parts (see initial dataset) 

The question is, using DAX,  how can I adjust the departure dates to make sure the safety stock is always kept?

 

Here is how the problem looks like in an example :

 

INITIAL DATA SET

DateTypeSafety Stock (after transaction)
1/1Departing3
1/2Departing2
1/4Departing1 (SO IT NEEDS TO WAIT)
1/5Departing0  (SO IT NEEDS TO WAIT)
1/6Arriving1
1/7Arriving2

 

 

I am looking to write a loop in DAX that would output the following (notice the 2 impossible departures have been move after each new arrival) 

 

FINAL DATA SET

DateTypeSafety Stock (after transaction)
1/1Departing3
1/2Departing2
1/6Arriving3 (ok 1 can leave) 
1/6Departing2
1/7Arriving 3 (ok another one can leave from the 2 that were previously impossible)
1/7Departing2

 

 

Is there a way to do that in a DAX Loop ?

 

Thanks for reading. 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This is something that DAX is not good at. It can handle iterating just fine but cannot calculate recursively.

 

This problem might be possible in DAX but isn't going to have a simple solution. @Jihwan_Kim has made a valiant effort but I suspect has included some assumptions that will prevent it from working for the general problem you're trying to solve.

 

Personally, having had both successes and failures in attempts to refactor recursive logic into valid DAX, I'd recommend using a different tool (e.g. Power Query) to solve this, even if it ultimately can be achieved in DAX with sufficient effort and creativity.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

This is something that DAX is not good at. It can handle iterating just fine but cannot calculate recursively.

 

This problem might be possible in DAX but isn't going to have a simple solution. @Jihwan_Kim has made a valiant effort but I suspect has included some assumptions that will prevent it from working for the general problem you're trying to solve.

 

Personally, having had both successes and failures in attempts to refactor recursive logic into valid DAX, I'd recommend using a different tool (e.g. Power Query) to solve this, even if it ultimately can be achieved in DAX with sufficient effort and creativity.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I assume you have Arrival-table like below.

And then, please try to create a new table by using the below DAX formula.

Please check the below picture, DAX formula for creating a table, and the attached pbix file.

 

 

 

Untitled.png

Final table = 
VAR _maxarrivingdate =
    MAX ( Arriving[Date] )
VAR _Datetable =
    SELECTCOLUMNS (
        GENERATESERIES ( DATE ( 2022, 1, 1 ), _maxarrivingdate ),
        "@Date", [Value]
    )
VAR _initialstock = 3
VAR _startdate =
    DATE ( 2022, 1, 1 )
VAR _transactiontable =
    UNION (
        ADDCOLUMNS (
            ADDCOLUMNS ( _Datetable, "@Type", "Departing" ),
            "@Transaction", SWITCH ( TRUE (), [@Date] = _startdate, _initialstock, -1 )
        ),
        ADDCOLUMNS ( Arriving, "@Transaction", 1 )
    )
VAR _stockleveltable =
    ADDCOLUMNS (
        _transactiontable,
        "@Stocklevel",
            IF (
                SUMX (
                    FILTER ( _transactiontable, [@Date] <= EARLIER ( [@Date] ) ),
                    [@Transaction]
                ) >= 2,
                SUMX (
                    FILTER ( _transactiontable, [@Date] <= EARLIER ( [@Date] ) ),
                    [@Transaction]
                ),
                2
            )
    )
VAR _mindateatleastlevel =
    MINX ( FILTER ( _stockleveltable, [@stocklevel] = 2 ), [@Date] )
VAR _newtable =
    SUMMARIZE (
        FILTER (
            _stockleveltable,
            NOT (
                [@Date] > _mindateatleastlevel
                    && [@Stocklevel] = 2
                    && NOT (
                        CONTAINS (
                            SUMMARIZE (
                                FILTER ( _stockleveltable, [@Date] = EARLIER ( [@Date] ) ),
                                [@Type]
                            ),
                            [@Type], "Arriving"
                        )
                    )
            )
        ),
        [@Date],
        [@Type],
        [@Transaction]
    )
VAR _addindexcolumn =
    ADDCOLUMNS (
        _newtable,
        "@index",
            RANKX ( _newtable, [@Date],, ASC ) * 10
                + IF ( [@Type] = "Arriving", 1, 2 )
    )
VAR _newstockleveltable =
    SUMMARIZE (
        ADDCOLUMNS (
            _addindexcolumn,
            "@newstocklevel",
                SUMX (
                    FILTER ( _addindexcolumn, [@index] <= EARLIER ( [@index] ) ),
                    [@Transaction]
                )
        ),
        [@Date],
        [@Type],
        [@newstocklevel]
    )
RETURN
    _newstockleveltable

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors