Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.