Hi - I appreciate some help to create some dax forecast formulas based on the following excel example tables below. This sample data contains year 2022 actual Purchase and Sales Sold data. And Stock Balance figure is computed as Stock Balance from previous month + Purchase - Sales.
Purchase Rates and Sold Rates are computed as a % of Stock Balance - and this is used as a Rate Table to compute Forecast Purchase and Sold for year 2023.
I am stuck on creating a working dax formulas for Forecast Purchase, Forecast Sold and Forecast Stock Balance. It is easy to do this on Excel - but with my limited Power BI knowledge - it is a challenge.
| Stock History | Purchase Data | Sold Data | Stock Balance (calculated Balance Beginning of Month + Purchase - Sales for the month) | Purchase Rate (Purchase / Stock Balance) | Sold Rate (Sold /Subscriber Balance) | |
| Jan-22 | 30,183 | 29,905 | 659,235 | 4.58% | 4.54% | |
| Feb-22 | 27,317 | 24,941 | 661,611 | 4.13% | 3.77% | |
| Mar-22 | 36,489 | 29,922 | 668,178 | 5.46% | 4.48% | |
| Apr-22 | 26,288 | 21,466 | 673,000 | 3.91% | 3.19% | |
| May-22 | 28,911 | 24,311 | 677,600 | 4.27% | 3.59% | |
| Jun-22 | 27,958 | 25,105 | 680,453 | 4.11% | 3.69% | |
| Jul-22 | 28,758 | 47,759 | 661,452 | 4.35% | 7.22% | |
| Aug-22 | 28,019 | 49,386 | 640,085 | 4.38% | 7.72% | |
| Sep-22 | 36,898 | 52,494 | 624,489 | 5.91% | 8.41% | |
| Oct-22 | 43,189 | 52,683 | 614,995 | 7.02% | 8.57% | |
| Nov-22 | 35,469 | 63,773 | 586,691 | 6.05% | 10.87% | |
| Dec-22 | 37,461 | 61,155 | 562,997 | 6.65% | 10.86% | |
| | | | | | | |
| | | | | | | |
| Stock Forecast | Forecast Purchase (Purchase rate * Previous Month Stock Balance) | Forecast Sold (Sold rate * Previous Month Stock Balance) | Forecast Stock Balance (carry forward actual Dec 2022 stock balance) | Purchase Rate (use monthly 2022 rates above) | Sold Rate (use monthly 2022 rates above) | Previous Month Stock Balance |
| Jan-23 | 25,777 | 25,539 | 563,234 | 4.58% | 4.54% | 562,997 |
| Feb-23 | 23,255 | 21,232 | 565,257 | 4.13% | 3.77% | 563,234 |
| Mar-23 | 30,869 | 25,313 | 570,813 | 5.46% | 4.48% | 565,257 |
| Apr-23 | 22,296 | 18,207 | 574,902 | 3.91% | 3.19% | 570,813 |
| May-23 | 24,529 | 20,626 | 578,805 | 4.27% | 3.59% | 574,902 |
| Jun-23 | 23,782 | 21,355 | 581,232 | 4.11% | 3.69% | 578,805 |
| Jul-23 | 25,270 | 41,967 | 564,535 | 4.35% | 7.22% | 581,232 |
| Aug-23 | 24,712 | 43,557 | 545,690 | 4.38% | 7.72% | 564,535 |
| Sep-23 | 32,242 | 45,870 | 532,062 | 5.91% | 8.41% | 545,690 |
| Oct-23 | 37,365 | 45,579 | 523,849 | 7.02% | 8.57% | 532,062 |
| Nov-23 | 31,670 | 56,942 | 498,576 | 6.05% | 10.87% | 523,849 |
| Dec-23 | 33,175 | 54,157 | 477,594 | 6.65% | 10.86% | 498,576 |
