Forum Discussion

JamilESpencer's avatar
JamilESpencer
Frequent Visitor
8 years ago

Calculate sales forecast using two tables

Hi,

 

I am trying to figure out a formula to calulate sales projections using two different tables. I created a relationship between the two tables via my "Item" column.

 

Table 1 contains previous fiscal year sales data

Table 2 contains expected percentage increase per month per ITEM

 

I created measures totalling each of my monthly sales data in Table 1 i.e. a measure for Total Sales in June, Total Sales in July etc. E.g. FM19Apr = CALCULATE(sum('Sales History'[Units Sold]),FILTER('Date','Date'[Fiscal Year]="FY18"),FILTER('Date','Date'[Month]=4)) 

 

I am trying calculate the sales increase per ITEM per Month. Note, I also have a data table.

 

E.g. Table 1:

TRANDATEITEMUnits Sold
2015-01-051261021408
2015-01-221261021204
2015-03-061261021204
2015-05-261261021408
2015-05-291261021204
2015-06-181261021204
2015-07-091261021204
2015-07-231261021408
2015-08-061261021204
2015-09-181261021204
2015-09-301261021408
2015-10-191261021204
2015-11-301261021204

 

 

E.g. Table 2:

ITEMJune JulyAugustSeptemberOctoberNovemberDecember
1261021       
1261023       
1261024       
1261026       
1261027       
1261028       
1261029       
12620210.1 0.10.1   
12620220.1 0.10.1   
12620230.1 0.10.1   
12620240.1 0.10.1   
12620250.1 0.10.1   
12620260.1 0.10.1   
12620270.1 0.10.1   
12810010.250.250.250.25 0.250.25
12810020.250.250.250.25 0.250.25
12810030.250.250.250.25 0.250.25
12810040.250.250.250.25 0.250.25