Need help to create some dax forecasting measures

Anonymous — Fri, 21 Jul 2023 07:34:15 GMT

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

Re: Need help to create some dax forecasting measures

some_bih — Sun, 23 Jul 2023 17:45:25 GMT

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 🙂

Re: Need help to create some dax forecasting measures

Anonymous — Mon, 24 Jul 2023 12:04:38 GMT

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 :

topic Re: Need help to create some dax forecasting measures in DAX Commands and Tips

Need help to create some dax forecasting measures

Re: Need help to create some dax forecasting measures

Re: Need help to create some dax forecasting measures