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.
Hi,
I would like to create a visual that will reflect weekly movements in a specific warehouse.
Incoming Inventory stores volumes of products that come into the warehouse, by date and product
Out Inventory stores the sales from specific warehouse,by dare and product
I'd like to create a matrix/table visual that will reflect weekly: New Arrivals QTY; Sales QTY; Stock on hand (RT). If the week filter is selected, the data must be shown with RT Stock for that specific week.
as an example:
Incoming Inventory Table
Week | Date | Product | Warehouse | Incoming QTY |
11 | 3/15/2021 | Orange | P0003 | 2 |
11 | 3/15/2021 | Apple | P0004 | 2 |
11 | 3/15/2021 | Orange | P0003 | 4 |
11 | 3/15/2021 | Banana | P0004 | 4 |
11 | 3/15/2021 | Orange | P0001 | 4 |
11 | 3/15/2021 | Banana | P0003 | 4 |
11 | 3/15/2021 | Banana | P0002 | 3 |
11 | 3/15/2021 | Apple | P0002 | 1 |
11 | 3/15/2021 | Banana | P0003 | 8 |
11 | 3/15/2021 | Orange | P0004 | 5 |
12 | 3/22/2021 | Apple | P0001 | 2 |
12 | 3/22/2021 | Banana | P0002 | 2 |
12 | 3/22/2021 | Banana | P0005 | 4 |
12 | 3/22/2021 | Orange | P0001 | 4 |
12 | 3/22/2021 | Apple | P0002 | 4 |
12 | 3/22/2021 | Orange | P0002 | 4 |
12 | 3/22/2021 | Apple | P0001 | 3 |
12 | 3/22/2021 | Banana | P0003 | 1 |
12 | 3/22/2021 | Orange | P0004 | 8 |
12 | 3/22/2021 | Banana | P0005 | 5 |
13 | 3/29/2021 | Apple | P0001 | 2 |
13 | 3/29/2021 | Banana | P0002 | 2 |
13 | 3/29/2021 | Banana | P0005 | 4 |
13 | 3/29/2021 | Orange | P0001 | 4 |
13 | 3/29/2021 | Apple | P0002 | 4 |
13 | 3/29/2021 | Orange | P0002 | 4 |
13 | 3/29/2021 | Apple | P0001 | 3 |
13 | 3/29/2021 | Banana | P0003 | 1 |
13 | 3/29/2021 | Orange | P0004 | 8 |
13 | 3/29/2021 | Banana | P0005 | 5 |
Out Inventory Table
Date | Product | Warehouse | QTY |
3/15/2021 | Orange | P0003 | 1 |
3/15/2021 | Apple | P0004 | 1 |
3/15/2021 | Orange | P0003 | 1 |
3/15/2021 | Banana | P0004 | 1 |
3/15/2021 | Orange | P0001 | 1 |
3/15/2021 | Banana | P0003 | 1 |
3/15/2021 | Banana | P0002 | 1 |
3/15/2021 | Apple | P0002 | 1 |
3/15/2021 | Banana | P0003 | 1 |
3/15/2021 | Orange | P0004 | 1 |
3/22/2021 | Apple | P0001 | 1 |
3/22/2021 | Banana | P0002 | 1 |
3/22/2021 | Banana | P0005 | 1 |
3/22/2021 | Orange | P0001 | 1 |
3/22/2021 | Apple | P0002 | 1 |
3/22/2021 | Orange | P0002 | 1 |
3/22/2021 | Apple | P0001 | 1 |
3/22/2021 | Banana | P0003 | 1 |
3/22/2021 | Orange | P0004 | 1 |
3/22/2021 | Banana | P0005 | 1 |
3/29/2021 | Apple | P0001 | 1 |
3/29/2021 | Banana | P0002 | 1 |
3/29/2021 | Banana | P0005 | 1 |
3/29/2021 | Orange | P0001 | 1 |
3/29/2021 | Apple | P0002 | 1 |
3/29/2021 | Orange | P0002 | 1 |
3/29/2021 | Apple | P0001 | 1 |
3/29/2021 | Banana | P0003 | 1 |
3/29/2021 | Orange | P0004 | 1 |
3/29/2021 | Banana | P0005 | 1 |
Expected result
Week | Week | Week | Week | Week | Week | Week | Week | Week | ||
11 | 11 | 11 | 12 | 12 | 12 | 13 | 13 | 13 | ||
Warehouse | Product | Incoming QTY | sales QTY | Stock | Incoming QTY | sales QTY | Stock | Incoming QTY | sales QTY | Stock |
P0001 | Apple | 5 | 2 | 3 | 5 | 2 | 6 | |||
P0001 | Orange | 4 | 1 | 3 | 4 | 1 | 6 | 4 | 1 | 9 |
P0002 | Apple | 1 | 1 | 0 | 4 | 1 | 3 | 4 | 1 | 6 |
P0002 | Banana | 3 | 1 | 2 | 2 | 1 | 3 | 2 | 1 | 4 |
P0002 | Orange | 4 | 1 | 3 | 4 | 1 | 6 | |||
P0003 | Banana | 12 | 2 | 10 | 1 | 1 | 10 | 1 | 1 | 10 |
P0003 | Orange | 6 | 2 | 4 | 4 | 4 | ||||
P0004 | Apple | 2 | 1 | 1 | 1 | 1 | ||||
P0004 | Banana | 4 | 1 | 3 | 3 | 3 | ||||
P0004 | Orange | 5 | 1 | 4 | 8 | 1 | 11 | 8 | 1 | 18 |
P0005 | Banana | 9 | 2 | 7 | 9 | 2 | 14 |
Thanks!
Solved! Go to Solution.
Hi,
Sorry that I misunderstood the Stock that has to actually show Stock RT.
I fixed the Stock -> Stock RT
Please check the link down below.
https://www.dropbox.com/s/a04vwkz4dmnm3xj/9g.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please check the link down below whether it is what you are looking for.
all measures are in the sample pbix file.
https://www.dropbox.com/s/a04vwkz4dmnm3xj/9g.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Thank you for your response,
however the solution does not fits the requirements. The main idea here is to have the stock as Running Total and once the Week filter is applied, the Total stock available in that specific week will be visible. Basically Iwant to be able to respond the following questions:
1. What came in?
2. What was sold?
3. What is the total stock available for NOW (specific week) in the warehouse (specific warehouse)
Thanks.
Hi,
Sorry that I misunderstood the Stock that has to actually show Stock RT.
I fixed the Stock -> Stock RT
Please check the link down below.
https://www.dropbox.com/s/a04vwkz4dmnm3xj/9g.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Thanks a lot for your response.
I was struggling with the quick measure for RT and didn't even thought to cut it a bit 🙂
Thanks again.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |