Forum Discussion
JRParker
Helper III
Helper IIIDAX Formula for Calculating Average Inventory Balance for past 6 months
Currently measure to calculate Monthly Total Inventory SUMX( FILTER( 'BS_Data', 'BS_Data'[Sub-Category] = "Inventory" || 'BS_Data'[Sub-Category] = "Inventory Reserves" ...
JRParkerHelper III
Helper IIIBelow data includes Total inventory for above measure and Average calculation:
| Sub-Category | Date | Account | Balance | Total | Average |
| Inventory | 2/28/2022 | 1000 | $173,755.36 | ||
| Inventory | 2/28/2022 | 2000 | $2,396,491.26 | ||
| Inventory Reserves | 2/28/2022 | 3000 | ($727,324.31) | $ 1,842,922.31 | $ 1,842,922.31 |
| Inventory | 3/31/2022 | 4000 | ($16,139.78) | ||
| Inventory | 3/31/2022 | 1000 | $127,339.02 | ||
| Inventory | 3/31/2022 | 5000 | $393.89 | ||
| Inventory | 3/31/2022 | 2000 | $2,464,234.37 | ||
| Inventory Reserves | 3/31/2022 | 3000 | ($727,324.32) | $ 1,848,503.18 | $ 1,845,712.75 |
| Inventory | 4/30/2022 | 4000 | $3,984.03 | ||
| Inventory | 4/30/2022 | 1000 | $45,977.16 | ||
| Inventory | 4/30/2022 | 5000 | $5,752.73 | ||
| Inventory | 4/30/2022 | 2000 | $2,612,699.21 | ||
| Inventory Reserves | 4/30/2022 | 3000 | ($727,324.32) | $ 1,941,088.81 | $ 1,877,504.77 |
| Inventory | 5/31/2022 | 1000 | $23,127.14 | ||
| Inventory | 5/31/2022 | 2000 | $2,543,720.99 | ||
| Inventory Reserves | 5/31/2022 | 3000 | ($724,651.48) | $ 1,842,196.65 | $ 1,868,677.74 |
| Inventory | 6/30/2022 | 1000 | $58,808.53 | ||
| Inventory | 6/30/2022 | 2000 | $2,542,381.53 | ||
| Inventory Reserves | 6/30/2022 | 3000 | ($721,131.09) | $ 1,880,058.97 | $ 1,870,953.98 |
| Inventory | 7/31/2022 | 1000 | $80,969.48 | ||
| Inventory | 7/31/2022 | 2000 | $2,471,854.94 | ||
| Inventory Reserves | 7/31/2022 | 3000 | ($730,192.13) | $ 1,822,632.29 | $ 1,862,900.37 |
| Inventory | 8/31/2022 | 1000 | $120,011.34 | ||
| Inventory | 8/31/2022 | 2000 | $2,655,650.40 | ||
| Inventory Reserves | 8/31/2022 | 3000 | ($806,739.13) | $ 1,968,922.61 | $ 1,883,900.42 |
| Inventory | 9/30/2022 | 1000 | $31,772.13 | ||
| Inventory | 9/30/2022 | 2000 | $2,769,393.21 | ||
| Inventory Reserves | 9/30/2022 | 3000 | ($743,358.78) | $ 2,057,806.56 | $ 1,918,784.32 |
| Inventory | 10/31/2022 | 1000 | $59,120.05 | ||
| Inventory | 10/31/2022 | 2000 | $3,064,282.38 | ||
| Inventory Reserves | 10/31/2022 | 3000 | ($744,559.05) | $ 2,378,843.38 | $ 1,991,743.41 |
| Inventory | 11/30/2022 | 1000 | $62,197.02 | ||
| Inventory | 11/30/2022 | 2000 | $3,164,264.98 | ||
| Inventory Reserves | 11/30/2022 | 3000 | ($793,079.90) | $ 2,433,382.10 | $ 2,090,274.32 |
| Inventory | 12/31/2022 | 1000 | $74,531.77 | ||
| Inventory | 12/31/2022 | 2000 | $3,143,180.69 | ||
| Inventory Reserves | 12/31/2022 | 3000 | ($792,709.63) | $ 2,425,002.83 | $ 2,181,098.30 |
| Inventory | 1/31/2023 | 1000 | $49,807.43 | ||
| Inventory | 1/31/2023 | 2000 | $3,317,395.47 | ||
| Inventory Reserves | 1/31/2023 | 3000 | ($794,295.07) | $ 2,572,907.83 | $ 2,306,144.22 |
| Inventory | 2/28/2023 | 4000 | ($1.53) | ||
| Inventory | 2/28/2023 | 1000 | $19,876.13 | ||
| Inventory | 2/28/2023 | 2000 | $3,459,801.51 | ||
| Inventory Reserves | 2/28/2023 | 3000 | ($798,473.36) | $ 2,681,202.75 | $ 2,424,857.58 |
| Inventory | 3/31/2023 | 1000 | $37,662.19 | ||
| Inventory | 3/31/2023 | 2000 | $3,472,822.01 | ||
| Inventory Reserves | 3/31/2023 | 3000 | ($767,511.49) | $ 2,742,972.71 | $ 2,571,093.64 |
| Inventory | 4/30/2023 | 1000 | $14,343.54 | ||
| Inventory | 4/30/2023 | 2000 | $3,303,697.47 | ||
| Inventory Reserves | 4/30/2023 | 3000 | ($757,092.97) | $ 2,560,948.04 | $ 2,569,402.71 |
| Inventory | 5/31/2023 | 1000 | $15,891.52 | ||
| Inventory | 5/31/2023 | 2000 | $3,202,456.96 | ||
| Inventory Reserves | 5/31/2023 | 3000 | ($762,636.58) | $ 2,455,711.90 | $ 2,573,124.34 |
| Inventory | 6/30/2023 | 1000 | $35,325.97 | ||
| Inventory | 6/30/2023 | 2000 | $3,131,172.31 | ||
| Inventory Reserves | 6/30/2023 | 3000 | ($790,504.02) | $ 2,375,994.26 | $ 2,564,956.25 |