Projecting Future Sales based on last year's week over week % change

[anthonyloh]

Hey guys,

Here's a tricky one. I'm trying to create a projection that takes the current year sold qty so far this year, and using last year's week over week % change, project how many sold units we will well for the remainder of the year. For the first week into the future I would take last week's sold qty and multiple it by (1 + % change) for the same week last year. For 2 weeks+ into the future I would then need to look at the previous week's projected sold qty and multiply that by (1 + % change) for the same week last year.

Attached is how the data would look like in excel and the Projected Qty column is the calculation I am trying to replicate in Power BI. The excel formula for that column is =IF(ISBLANK(D21), E21*(1+C22), D21*(1+C22))

[v-diye-msft]

Hi @anthonyloh 

Kindly make the table as copyable will be easier for me to repro the data. 

It seems similar to the case I solved yesterday, check it out here:

https://community.powerbi.com/t5/Desktop/Calculating-based-on-previous-row-same-column/m-p/1164684#M... 

Also here're some other threads could be your reference:

https://community.powerbi.com/t5/Desktop/Calculate-Column-base-on-result-previous-result-row-divide/... 

https://community.powerbi.com/t5/Desktop/Using-Previous-rows-result-for-next-row-calculation/m-p/926... 

I created a dummy sample as below, kindly check the results: (Pbix attached)

Column = IF([SoldQty]<>BLANK(),MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[SoldQty])*(1+[%change]),MAXX(FILTER('Table',[Index]=EARLIER('Table'[Index])-1),[ProjectedQty])*(1+[%change]))

[anthonyloh]

@v-diye-msft Here is the data:

Calendar Week Key	Sold Qty LY	% Change WOW	Sold Qty TY	Projected Qty
202005	24		141
202006	473	18.7083333	1,508
202007	512	0.0824524	1,495
202008	642	0.2539063	1,347
202009	763	0.1884735	1,564
202010	1,419	0.8597641	1,509
202011	1,983	0.3974630	1,316
202012	1,836	-0.0741301	671
202013	1,775	-0.0332244	1,030
202014	1,760	-0.0084507	1,139
202015	1,865	0.0596591	1,057
202016	2,132	0.1431635	2,505
202017	2,173	0.0192308	2,046
202018	2,205	0.0147262	1,363
202019	2,163	-0.0190476	2,693
202020	1,937	-0.1044845	2,230
202021	2,065	0.0660816	3,217
202022	2,274	0.1012107	2,399
202023	2,008	-0.1169745	1,923
202024	2,458	0.2241036	1,957
202025	3,822	0.5549227		3,043
202026	2,574	-0.3265306		2,049
202027	5,621	1.1837607		4,475
202028	3,639	-0.3526063		2,897
202029	4,089	0.1236603		3,256
202030	3,677	-0.1007581		2,928
202031	3,821	0.0391624		3,042
202032	3,126	-0.1818896		2,489
202033	3,586	0.1471529		2,855
202034	2,740	-0.2359175		2,182
202035	2,439	-0.1098540		1,942
202036	1,809	-0.2583026		1,440
202037	1,735	-0.0409066		1,381

I looked at your calculation and I think you misunderstood which column I am calculating. I am trying to calculate the Projected Qty column in Power BI and in the data above example I am simply showing what the expected result should look like. In your data model you have Projected Qty as manually entered data and then you're calculating something else. 

So if we are currently in week 202025 the calculation for projected qty would be 1957 (last week of actual sales) * (1 + 0.555) (the change % for week 202025. For week 202026 and onward, the projected qty would be 3043 (last week's projected qty) * (1+ -0.327) (the change % for week 202026. 

Thank you,

Anthony