Forum Discussion

Reddyp's avatar
Reddyp
Helper I
4 years ago
Solved

Inventory aging report

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 1...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

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

     

jsaunders_zero9's avatar
jsaunders_zero9
Responsive Resident
4 years ago

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