Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Hi @Anonymous I am keen to help you, for start
amount
659,235 |
is calculated like some balance X + purc - sold (this is ok) but where is X, openning balance for real start of your calculation?
PS I am on holiday, so my answers could be lateeeee 🙂
Proud to be a Super User!
Hi - many thanks for helping out. There is actually a 3 month prior to year 2022 for actual purchases - so the last three months of year 2021 moving on year 2022 looks like the snapshot below. Actual sales starts year 2022 - this is why i want to use year 2022 rates to predict 2023 forecasts :
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |