Inventory Stock_Balance and cost calculation_DAX

[Reddyp]

Hi, 

I have some store data and need DAX to calculate remaining stock in iventory along with purchase cost

Example: 

In this table I have two Branches and each has two procucts, On Jan the total purchase of I10 product in Branch1 is 10 and cost per unit is $21. when there us no sale of I20 on Jan in the Branch1 then this count should come under "Last month remaining quantity" on Feb
Note: Out of the 10 "I10" products if 2 sold on Jan then the remaining count "8" should come under "Last month remaining quantity" on Feb along with purchased cost

DAX should get result into "Last month remaining quantity" after filtering Branch and Products

Product	Branch	Month	Type	Quantity	Amount	Last Month remaining quantity	Price per Unit
I10	Branch1	Jan	Purchase	10	21
I10	Branch1	Feb	Purchase	10	49	10	21
I10	Branch1	Mar	Purchase	10	29	10	49
I10	Branch1	Apr	Purchase	10	46	10	29
I20	Branch1	May	Purchase	10	47
I20	Branch1	June	Purchase	10	22	10	47
I20	Branch1	July	Purchase	10	17	10	22
I20	Branch1	Aug	Purchase	10	14	10	17
I10	Branch2	Jan	Purchase	10	30
I10	Branch2	Feb	Purchase	10	40	10	30
I10	Branch2	Mar	Purchase	10	23	10	40
I10	Branch2	Apr	Purchase	10	40	10	23
I20	Branch2	May	Purchase	10	29
I20	Branch2	Jun	Purchase	10	31	10	29
I20	Branch2	Jul	Purchase	10	28	10	31
I20	Branch2	Aug	Purchase	10	31	10	28

Thank you!

[tamerj1 · Accepted solution]

Hi Reddy

Not sure how the real data looks like and some info is missing such as the the quantity sold. However, based on the available information and data:

1st you need to have a month number column. Create a new column 

Month Number =
VAR CurrentMonth = Stores[Month]
RETURN
    SWITCH (
        TRUE (),
        CurrentMonth = "Jan", 1,
        CurrentMonth = "Feb", 2,
        CurrentMonth = "Mar", 3,
        CurrentMonth = "Apr", 4,
        CurrentMonth = "May", 5,
        CurrentMonth = "June", 6,
        CurrentMonth = "July", 7,
        CurrentMonth = "Aug", 8,
        CurrentMonth = "sep", 9,
        CurrentMonth = "Oct", 10,
        CurrentMonth = "Nov", 11,
        CurrentMonth = "Dec", 12
    )

For last month quantity create a new column

Last Month remaining quantity =
VAR StoreBranchTable =
    CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
    MINX (
        FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
        Stores[Quantity]
    )

For last month amont create a new column

Price per Unit =
VAR StoreBranchTable =
    CALCULATETABLE ( Stores, ALLEXCEPT ( Stores, Stores[Product], Sores[Branch] ) )
VAR CurrentMonthNum = Stores[Month Number]
RETURN
    MINX (
        FILTER ( StoreBranchTable, Stores[Month Number] = CurrentMonthNum - 1 ),
        Stores[Amount]
    )