Forum Discussion
Monthly Inventory Value
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...
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.