8 Replies Latest reply on Nov 15, 2016 2:05 PM by Gert-Jan Neeft

# Sum Sales between two variable dates

Hi, I dont know how to do this and cant find the right formula for a calculated field.

Here is the issue:

• I have numerous accounts that each have individual contract start and end dates.  The start dates are dimensions called "2015 Contract Start Date" and the end dates are dimensions called "2015 Contract End Date"
• I would like to look at these accounts on the Measure of "Sales\$"

Question: How can I sum "Sales\$" for each account between the two date dimensions of "2015 Contract Start Date" and "2015 Contract End Date"?

Here is a screen shot.

• ###### 1. Re: Sum Sales between two variable dates

It would be easier to explain if there is a workbook.

Try pivoting the measure group then drag ID-No to rows and the pivoted value field to the column shelf to see the sales...

• ###### 2. Re: Sum Sales between two variable dates

I agree, please forward a sample workbook, or if you can provide a screen shot of your dimensions and measures, that would help.

• ###### 3. Re: Sum Sales between two variable dates

Thanks for the help.  Here is the packaged workbook.

1 of 1 people found this helpful
• ###### 4. Re: Sum Sales between two variable dates

Jeff, is this what you would like?  Grand Totals of the Rows?  If so just select Analysis->Totals-> and show row grand totals

1 of 1 people found this helpful
• ###### 5. Re: Sum Sales between two variable dates

Shevata Shanmugam, No.  I am trying to sum the sales for the period between the 2015 Contract Start Date and the 2016 Contract End Date, which is one contract year.  For example, for account 2000-1117 I want to only show sales between the dates of January 31st 2015 and January 30th 2016.

1 of 1 people found this helpful
• ###### 6. Re: Sum Sales between two variable dates

Is this what you are looking for?

• ###### 7. Re: Sum Sales between two variable dates

No, It looks like that just takes out the Gen Prod Posting Group.

I am wanting to only see the sales booked between the Contract Start Date and the Contract End Date.  Almost like a filter for that contract year.

The general question I am trying to answer is: What are the sales for each account based on their individual contract year which starts on the Contract Start Date and ends on the Contract End Date.

• ###### 8. Re: Sum Sales between two variable dates

Is this what you're looking for?

You can drop the Gen pill to the column shelf if you want.

1 of 1 people found this helpful