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

[bklyn3]

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

Date	Type	Safety Stock (after transaction)
1/1	Departing	3
1/2	Departing	2
1/4	Departing	1 (SO IT NEEDS TO WAIT)
1/5	Departing	0  (SO IT NEEDS TO WAIT)
1/6	Arriving	1
1/7	Arriving	2

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

Date	Type	Safety Stock (after transaction)
1/1	Departing	3
1/2	Departing	2
1/6	Arriving	3 (ok 1 can leave)
1/6	Departing	2
1/7	Arriving	3 (ok another one can leave from the 2 that were previously impossible)
1/7	Departing	2

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

Thanks for reading. 

[AlexisOlson · Accepted solution]

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]

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.

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

[Itsbibin]

hi @Jihwan_Kim 
Ihave 2 table in my Power BI report B-Details and SPResult. SP result table is a stored procedure and will change dynamically with parameters. I want to create a new table from this in which it should filter Name and Country from B-Details and SPResult. Now after filtering this we have a uniquekey in B-Details. Now in the new table for each UniqueKey we have to get all the filtered values from SPResult table. So in the new table UniqueKey key row will be duplicated with as much rows we have in SPResult. Can you help me how to fix this?