Hello everyone,
I'm developing a tool to calculate the cost associated with inventory storaged at a 3d supplier. The cost is obtained by multipling the stock of each day by its weight and a fare:
Cost_day = (Stock Qty) * (Weight/piece) * (Fare)
However, the 'Stock Qty' that should be used to evaluate this cost is not all inventory available, but some amout. This happens because some stock are held at the site, and the rest should be at the 3d supplier (the latter is used to calculate the cost). This split is given by a parameter set by the user (I called D+?), which will define how much coverage in days should be considered and kept at the site. Another restriction is that, when some amout of stock is moved from the 3d supplier to the site, this amout needs to be a multiple value of its package (i.e. if I need 3 parts, and the package for that material is 4, the transfer must be of 4 parts).
The data source I have give me Initial Stock, Dates, Requirement, Invoices and and Pieces by Package. Using SUMX, TOPN and a few other expressions I was able to calculate the Running Stock (Stock + Invoices - Requirement) for each date. Using GENERATESERIES I was able to create the 'D+?' parameter and the 'D+? Requirement' and got the expected results.
The problem I'm facing is that the stock that should be transfered from the 3d supplier to the site today depends on the amout of stock that I had yesterday, and so on. I was able to create this model in Excel, and it works fine because I can easily make a reference to the previous row on the same column, but this is not true for PBI. For this to work in Excel, I have adopted the premise that, for the first 'Date' entry for each Material, the Stock at the site will be the 'D+?' stock rounded by Pieces by Package.
The mathematical expressions for Excel:
Corrected Requirement (Package): Roundup( ( ('D+? Requirement' + 'Requirement (Today)' ) - 'Last_day_stock' ) , 'Pieces by Package' )
Site_Stock_EndOfDay = Last_day_stock + Stock_to_transfered * 'Pieces by Package' - Requirement (Today)
The table below shows the expected results. The last columns in red headers are the ones I need help with.
| Date | Requirement | Requirement (Package) | Running Stock | Requirement (D+2) | Requirement (D+2) (Package) | Total Requirement (D+2) | Total Requirement (D+2) (Package) | Corrected Requirement | Corrected Requirement (Package) | Site_Stock_EndOfDay | 3d_Supplier_Stock_EndOfDay |
| Initial Stock | | | 106 | | | | | | | 20 | 86 |
| 27/03/2021 | 6 | 2 | 100 | 11 | 3 | 17 | 4 | 0 | 0 | 14 | 86 |
| 28/03/2021 | 5 | 1 | 95 | 12 | 3 | 17 | 4 | 3 | 1 | 14 | 81 |
| 29/03/2021 | 6 | 2 | 89 | 8 | 2 | 14 | 3 | 0 | 0 | 8 | 81 |
| 30/03/2021 | 6 | 2 | 83 | 12 | 3 | 18 | 4 | 10 | 2 | 12 | 71 |
| 31/03/2021 | 2 | 1 | 81 | 14 | 3 | 16 | 4 | 4 | 1 | 15 | 66 |
| 01/04/2021 | 10 | 2 | 71 | 8 | 2 | 18 | 4 | 3 | 1 | 10 | 61 |
| 02/04/2021 | 4 | 1 | 67 | 5 | 1 | 9 | 2 | 0 | 0 | 6 | 61 |
| 03/04/2021 | 4 | 1 | 63 | 5 | 1 | 9 | 2 | 3 | 1 | 7 | 56 |
I appreciate any help I can get.
Thanks!
