Seek help in recursive inventory dax

[LisaChung]

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.

[LisaChung]

Thanks. It is very helpful.

[Jihwan_Kim · Accepted solution]

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] )
        )
    )