Inventory aging report

[Reddyp]

I need to do this inventory calculation for individual branch (Example: if stock sold in branch 1 then the count of sold should deduct from Branch 1)

Example: I have received stock to B ranch 1 on 1st, 2nd, and 3rd and the stock is 10 shirts per day and total stock is 30 now 
and on 4th 15 shirts sold 

desired result: the sold count should deduct fromn first in date(the count sold is 15 on 4th so 10 should be decuted from 1st date and 5 shoud be deducted on 2nd)
and in the same way when 7 shirts sold on 5th date then 5 deducted from 2nd date (remaining in 2nd date) and 2 should deduct from 3rd date

Note: Calculation should be done based on Branch
Example: 

Date	Branch	New Stock	Stock out	Result
01-01-2022	Branch 1	10		0
02-01-2022	Branch 1	10		0
03-01-2022	Branch 1	10		8
04-01-2022	Branch 1		15
05-01-2022	Branch 1		7
06-01-2022	Branch 2	10		0
07-01-2022	Branch 2	10		5
08-01-2022	Branch 2	10		10
09-01-2022	Branch 2		15
10-01-2022	Branch 3	10		0
11-01-2022	Branch 3		10

[tamerj1 · Accepted solution]

Hi @Reddyp 
Here is a sample file https://we.tl/t-fIVAYRKzHD
One way is as follows: New Column >

Result = 
VAR NewStock = 
    SUMX ( 
        FILTER ( 
            CALCULATETABLE ( Stock, ALLEXCEPT ( Stock, Stock[Branch] ) ),
            Stock[Date] <= EARLIER ( Stock[Date] )
        ),
        Stock[New Stock]
    )
VAR StockOut =
    CALCULATE ( SUM ( Stock[Stock out] ), ALLEXCEPT ( Stock, Stock[Branch] ) )
VAR Difference = NewStock - StockOut
RETURN
    IF (
        NOT ISBLANK ( Stock[New Stock] ),
        IF ( 
            Difference <= 0, 
            0, 
            IF ( NewStock - Stock[New Stock] > StockOut, Stock[New Stock], Difference )
        )
    )

[Anonymous]

Thank you for providing the solution, However, I am experiencing a different problem. the code does not work for a few rows.

I tried tweking around, but I had no luck so far. any help regarding this matter will be a huge help. Thank you.

[Reddyp]

@tamerj1 Thank you somuch!
Can you pleaase suggest me best place to learn DAX, I don't want to be a blind person(just copy paste), I want to read and understand code. 

[tamerj1]

@Reddyp 

Start from the "Definitive Guide to DAX"

[Reddyp]

@jsaunders_zero9 Thank you for the reply, Can you please helpme with the DAX code
I have already received response from @Greg_Deckler for my previous question in calculating stock in and stock out for one branch (https://community.powerbi.com/t5/Desktop/Inventory-stock/td-p/2431824)
Request: But now I have different branches in my data and I need help in modifing DAX code provided by @Greg_Deckler 

@Greg_Deckler @jsaunders_zero9

@Ashish_Mathur @tamerj1 If possible please help me out, this is little urgent for me to close my work.

[jsaunders_zero9]

Hi @Reddyp 

My solution is in Power Query, I will leave you with the DAX experts seeing as you are halfway there.

Thank you

[jsaunders_zero9]

Hi @Reddyp 

This is the result you are looking for?

The steps I completed in PQ are in this file - Reddyp_sample.pbix

The file is a little rough but it might provide you some inspiration, essentially we are doing the following.

Creating 2 new queries - stock in and stock out.

Expanding those queries to be 1 item per line, so for stock in 1/01/2022 you end up with 10 lines.

Adding an index for each line in branch groups

Repeating the same steps for Stock out

Merging stock in query back to stock out query on branch and index.

Grouping by date and branch to sum up remaining stock from each stock in.

merging that all back to the master table.

It should hopefully make more sense when you look through the steps in the file.

Thank you