Calculate Sum Basis Data Availability for another Dimension

[mahendrankr]

Hi Team,

I have two tables .

Table 1) Sales Value of two products available for different period (Clean X untill Jun and Battery untill Apr).

Table 2) Has the cost for both these products but available untill Dec . 

Question is how do i calcuate the Profit (Sales-Cost)  for the period only untill sales value is available for each product. 

Sample Table below:

Month	Country	Products	Sales		Month	Country	Products	Cost
Jan	UKI	Clean X	10,959,685		Jan	UKI	Clean X	10,959,685
Feb	UKI	Clean X	5,672,042		Feb	UKI	Clean X	5,672,042
Mar	UKI	Clean X	7,903,795		Mar	UKI	Clean X	7,903,795
Apr	UKI	Clean X	4,061,328		Apr	UKI	Clean X	4,061,328
May	UKI	Clean X	6,208,527		May	UKI	Clean X	6,208,527
Jun	UKI	Clean X	3,067,256		Jun	UKI	Clean X	3,067,256
Jan	USA	Battery	10,959,685		Jul	UKI	Clean X	10,959,685
Feb	USA	Battery	5,672,042		Aug	UKI	Clean X	5,672,042
Mar	USA	Battery	7,903,795		Sept	UKI	Clean X	7,903,795
Apr	USA	Battery	4,061,328		Oct	UKI	Clean X	4,061,328
					Nov	UKI	Clean X	3,067,256
					Dec	UKI	Clean X	10,959,685
					Jan	UKI	Battery	10,959,685
					Feb	UKI	Battery	5,672,042
					Mar	UKI	Battery	7,903,795
					Apr	UKI	Battery	4,061,328
					May	UKI	Battery	6,208,527
					Jun	UKI	Battery	3,067,256
					Jul	UKI	Battery	10,959,685
					Aug	UKI	Battery	5,672,042
					Sept	UKI	Battery	7,903,795
					Oct	UKI	Battery	4,061,328
					Nov	UKI	Battery	3,067,256
					Dec	UKI	Battery	10,959,685

Thanks in advance.

[Or]

If these are two separate data tables, and the identical-name fields function as keys, and there is always a value for every month up until the last one (so, if the last month is November, May can't be missing), you could get away with just a simple sum:

Sum(Sales - Cost)

This is because any time the Sales part of the equation is missing (null), then Sales - Cost is null. I've demonstrated using a partial, simplified dataset below - note that the 1000 cost for Battery in month 3 does not factor into the result on the left.

If your data doesn't match this description and this was an oversimplified example, please elaborate on the actual structure as another solution may be required.

[mahendrankr]

I am computing the profit in a KPI box. So by default without any filters,

"the sum of sales" - "sum of cost only untill the period sales is available" is what i am looking for. And this may vary from product to product. therefore in the kpi box, the cost shoudl be taken only for those periods for which the sales is available for each product. i hope i am elaborative now.