Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
|
Solved! Go to 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 )
)
)
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 )
)
)
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.
@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.
@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.
Hi @Reddyp
My solution is in Power Query, I will leave you with the DAX experts seeing as you are halfway there.
Thank you
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |