cancel
Showing results for
Did you mean:

## 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

 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 ?

1 ACCEPTED SOLUTION
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.

2 REPLIES 2
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.

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.

``````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 ( _Datetable, "@Type", "Departing" ),
"@Transaction", SWITCH ( TRUE (), [@Date] = _startdate, _initialstock, -1 )
),
ADDCOLUMNS ( Arriving, "@Transaction", 1 )
)
VAR _stockleveltable =
_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]
)
_newtable,
"@index",
RANKX ( _newtable, [@Date],, ASC ) * 10
+ IF ( [@Type] = "Arriving", 1, 2 )
)
VAR _newstockleveltable =
SUMMARIZE (
"@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.