Need help with creating measures that calculates total value (total_cost) of remaining item and average unit cost (avg_cost). These measures are for last remaining qty, so it should be based on latest date (working from latest to old). for below example, takes dec 25 units first, then dec 15, and last remaining from nov 20.
| Date | Qty | Price | | | | | |
| 10-May-18 | 100 | 750.25 | | | | | |
| 10-Apr-18 | 50 | 800.65 | | | | | |
| 20-Jul-18 | 1000 | 780.25 | | | | | |
| 5-Aug-18 | 500 | 900.55 | | | | | |
| 9-Sep-18 | 400 | 760.95 | | | | | |
| 10-Oct-18 | 200 | 876.25 | | | | | |
| 25-Oct-18 | 400 | 800.65 | | Qty Left | 1800 | | |
| 6-Nov-18 | 600 | 780.25 | | | | | |
| 10-Nov-18 | 200 | 900.55 | | qty | price | Total | Avg |
| 20-Nov-18 | 500 | 760.95 | | 100 | 760.95 | 76095.00 | |
| 15-Dec-18 | 700 | 876.25 | | 700 | 876.25 | 613375.00 | |
| 25-Dec-18 | 1000 | 800.65 | | 1000 | 800.65 | 800650.00 | |
| | | | | | | 1490120.00 | 827.84 |
| | | | | | | | |
| | | | | measure_avg_cost | 827.84 | |
| | | | | measure_total_cost | 1490120.00 | |
Table contains 3 columns:
Date -- date units added to inventory
Qty -- qty added to inventory
Price -- price for 1 unit
qty_remaining -- calculated measure - current remaining qty
want to create following calculated measures:
avg_cost -- remaining qty avgerage unit cost
total_cost -- remaining qty total cost
Thank You
