topic Re: Seek help in recursive inventory dax in DAX Commands and Tips

Seek help in recursive inventory dax

Anonymous — Wed, 26 Oct 2022 05:36:58 GMT

Hi,

I would like to seek for help in creating recursive inventory stock logic.

Currently this is the example table we have.

Product	Country	Year/Month	Initial Stock	Stock on hand	intransit	demand	Balance
A	ABC	2022/10	0	100	50	30	120
A	ABC	2022/11	120	30	50	30	170
A	ABC	2022/12	170	30	50	30	220

We would like to create 2 recursive dax
1. Balance = initial stock + stock on hand + intransit - demand
2. Initial stock = (last month's) Balance + stock on hand + intransit - demand

The known data in the table are
1. Initial stock of 2022/10 : 0 (as the starting point)
2. Stock on hand, intransit, demand, product, country in all periods

Thanks for your help.

Re: Seek help in recursive inventory dax

Jihwan_Kim — Wed, 26 Oct 2022 06:45:19 GMT

Hi,

please check the below picture and the attached pbix file.

Balance measure: = CALCULATE ( SUMX ( Data, Data[Stock on hand] + Data[Intransit] - Data[Demand] ), FILTER ( ALL ( Data ), Data[Date] <= MAX ( Data[Date] ) ), VALUES ( Data[Country] ), VALUES ( Data[Product] ) )

Initial stock measure: = VAR _previousdate = MAXX ( FILTER ( ALL ( Data ), Data[Product] = MAX ( Data[Product] ) && Data[Country] = MAX ( Data[Country] ) && Data[Date] < MAX ( Data[Date] ) ), Data[Date] ) RETURN IF ( SELECTEDVALUE ( Data[Date] ) = DATE ( 2022, 10, 1 ), 0, CALCULATE ( [Balance measure:], FILTER ( ALL ( Data ), Data[Date] = _previousdate ), VALUES ( Data[Country] ), VALUES ( Data[Product] ) ) )

Re: Seek help in recursive inventory dax

Anonymous — Thu, 27 Oct 2022 00:26:07 GMT

Thanks. It is very helpful.