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 have data with weekly inventory values per product and store. What I need to get to is the below 2 measures:
1. Average Total Inventory by Product per Month
2. Last/Latest Total Inventory by Product per Month
Sample source data:
Date | Item | Store | SOH |
7/02/2022 | A1 | Melbourne | 50 |
7/02/2022 | A1 | Sydney | 90 |
14/02/2022 | A1 | Melbourne | 60 |
14/02/2022 | A1 | Sydney | 100 |
21/02/2022 | A1 | Melbourne | 40 |
21/02/2022 | A1 | Sydney | 120 |
28/02/2022 | A1 | Melbourne | 55 |
28/02/2022 | A1 | Sydney | 80 |
7/03/2022 | A1 | Melbourne | 20 |
7/03/2022 | A1 | Sydney | 70 |
14/03/2022 | A1 | Melbourne | 10 |
14/03/2022 | A1 | Sydney | 80 |
21/03/2022 | A1 | Melbourne | 5 |
21/03/2022 | A1 | Sydney | 100 |
28/03/2022 | A1 | Melbourne | 50 |
28/03/2022 | A1 | Sydney | 80 |
4/04/2022 | A1 | Melbourne | 40 |
4/04/2022 | A1 | Sydney | 90 |
7/02/2022 | B2 | Melbourne | 10 |
7/02/2022 | B2 | Sydney | 500 |
14/02/2022 | B2 | Melbourne | 10 |
14/02/2022 | B2 | Sydney | 400 |
21/02/2022 | B2 | Melbourne | 5 |
21/02/2022 | B2 | Sydney | 410 |
28/02/2022 | B2 | Melbourne | 0 |
28/02/2022 | B2 | Sydney | 350 |
7/03/2022 | B2 | Melbourne | 50 |
7/03/2022 | B2 | Sydney | 500 |
14/03/2022 | B2 | Melbourne | 45 |
14/03/2022 | B2 | Sydney | 450 |
21/03/2022 | B2 | Melbourne | 40 |
21/03/2022 | B2 | Sydney | 100 |
28/03/2022 | B2 | Melbourne | 35 |
28/03/2022 | B2 | Sydney | 150 |
4/04/2022 | B2 | Melbourne | 50 |
4/04/2022 | B2 | Sydney | 150 |
Required Results:
Average Total Inventory by Product per Month
February | March | April | |
A1 | 74.375 | 51.875 | 65 |
B2 | 195.83 | 171.25 | 100 |
Last/Latest Total Inventory by Product per Month
February | March | April | |
A1 | 135 | 130 | 130 |
B2 | 350 | 185 | 200 |
Hoping someone can point me in the right direction to achieve this! Thanks 😁
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
Im having similiar requirement, can you send me the pbix file, couldnt download the file from onedrive as it got deleted
Regards
Anand
Hi,
I do not have the files. Share some data, explain the question and show the expected result.
i have d 365 inventtranstable where it has item out & item in transactions:-
sample table:-
DATEFINANCIAL | CATEGORY | inventory value |
01-01-2024 00:00 | pepsi | -10364.27 |
02-01-2024 00:00 | coke | 500000000 |
03-01-2024 00:00 | fanta | -69323.41 |
04-01-2024 00:00 | thumsup | -64566.39 |
05-01-2024 00:00 | pepsi | 0 |
06-01-2024 00:00 | maaza | 566666 |
07-01-2024 00:00 | pepsi | -79063.53 |
Expected result in powerbi matrix visualization:-
CATEGORY | Jan | feb | mar |
pepsi | 50000 | 100000 | 2000000 |
coke | 40000 | 60001 | 50002 |
maaza | 70000 | 820100 | 700100 |
sprite | 90000 | 920100 | 1000100 |
thumsup | 50000 | 820100 | 900100 |
need to create measure to retrive those monthly snapshot values from daily transactions at the end of month or 1st day of next month( anything is fine).
im getting correct values using this sql query but having trouble to replicate the logic in sql/powerbi:- SELECT SUM(InventoryValue) from MONTHLYINVENTORYVALUE
WHERE CATEGORY= 'pepsi'
AND DATEFINANCIAL <='2024-02-01';
I do not see any link between the input and output tables. Based on the input table, show the expected result.
Thanks Ashish. The Last Total Inventory calculation is working well but the Average Total Inventory is not working with my actual data, I'm getting very low figures which don't add up.
You are welcome. I cannot say why my formulas are not working with your data. From the file that i have shared with you, my calculations work fine and tally with the your expected result. How can i help you further?
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 |
---|---|
97 | |
97 | |
81 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |