Forecasting this year's daily sales based on last year's percent complete

[bechi]

Hi! I'm trying to create a daily forecast for sales based on last year's percent complete between October and December. In other words, if cumulative sales on this day last year represented 10% complete last year, I want to be able to be able to forecast the total sales we expect to end up with and then, using last year's daily % complete, forecast what daily sales would look like if they followed the same daily % complete as last year. I mocked up some dummy data below and in this Dummy data for daily forecast file. Results would look something like the table below, but going through the end of December. Any help would be greatly appreciated!

Month Day	This year sales	Cumulative this year sales	Forecasted Sales	Cumulative % 1 Yr Revenue	Sales 1 Yr Ago
1-Oct	$682,067	$682,067	$682,067	0.0108688	$810,315
2-Oct	$577,821	$1,259,888	$1,259,888	0.0192016	$621,249
3-Oct	$824,018	$2,083,906	$2,083,906	0.0275767	$624,396
4-Oct	$830,449	$2,914,355	$2,914,355	0.0371482	$713,598
5-Oct	$725,546	$3,639,901	$3,639,901	0.0499106	$951,491
6-Oct	$757,971	$4,397,872	$4,397,872	0.0609925	$826,206
7-Oct	$686,591	$5,084,463	$5,084,463	0.0728939	$887,300
8-Oct	$607,627	$5,692,090	$5,692,090	0.0859474	$973,196
9-Oct	$518,373	$6,210,463	$6,210,463	0.0949903	$674,185
10-Oct	$817,283	$7,027,746	$7,027,746	0.1048278	$733,433
11-Oct	$879,377	$7,907,123	$7,907,123	0.1146099	$729,291
12-Oct	$678,086	$8,585,209	$8,585,209	0.1246514	$748,638
13-Oct	$637,021	$9,222,230	$9,222,230	0.1351502	$782,732
14-Oct	$662,635	$9,884,865	$9,884,865	0.146409	$839,396
15-Oct	$470,992	$10,355,857	$10,355,857	0.1576202	$835,841
16-Oct	$522,056	$10,877,913	$10,877,913	0.1662819	$645,769
17-Oct	$537,921	$11,415,834	$11,415,834	0.1749805	$648,514
18-Oct	$580,689	$11,996,523	$11,996,523	0.1850124	$747,928
19-Oct	$133,738	$12,130,261	$12,130,261	0.1941659	$682,427
20-Oct	$0		$12,837,372	0.2054844	$843,842
21-Oct	$0		$13,526,794	0.2165198	$822,742
22-Oct	$0		$14,140,298	0.22634	$732,137
23-Oct	$0		$14,576,190	0.2333172	$520,179
24-Oct	$0		$15,056,044	0.2409981	$572,649

[v-shex-msft]

Hi @bechi,

You can extract the current date and use it as a variable to get the previous year's date value. Then you can use this date and current category to look up and summary results.

For the calculation to finish in December, you can add a condition to extract the current date year part values as a condition to compare with the date field, then the calculations will be finished at the end of the year.

Regards,

Xiaoxin Sheng