Calculating Amount between two time periods Syntax help
I have a pivot chart with Product, Contract Number and Contract End Date listed on the Y Axis (rows) and months (Jan 2013, Feb 2013 ... Dec 2014) on the X Axis (Columns). Contracted amount by month (sum(Amount)) is the expression.
So the chart looks like as shown below
Product ContractNo. ContractEndDate YearMonth Jan 2013 Feb 2013 Mar 2013 .....Dec 2014
Prod A 000001 10/31/2015 $100 $200 $50 ..... $133
Prod B 000005 4/30/2014 $50 $75 $60 ..... $20
If we assume the Closed Month = August 2013, I want to create a calculation (Total Remaining Amount) (a column next to Contract End Date) that sums up all amounts From Sep 2013 upto 10/31/2015 for Prod A and upto 4/30/2014 for Prod B
The calculation that I need help with the syntax in words is
Sum All Amounts between Sep 2013 (i.e. ClosedMonth +1) and Contract End Date
Thanks for your response. the trouble with that expression is that it compares month with a year and month combination and returns everything under the sun.
I tried something like that myself before posting the question as given below
if(Month>= (Month($(vVersionMonthEnd)) +1) AND Month(YearMonth)<=Month(ContractEndDate) ,Amount))
where vVersionMonthEnd returns the version closed date like 2013/08/31, column Month returns Jan Feb Mar...
The trouble with that expression is that when I put it in a straight chart to view values by month, values for months Jan- Aug for each year is stripped off, when I want those months to be stripped off only for the current year.
the trick here is successfully comparing year and month from the YearMonth column which has values like 2013-01, 2013-02 and so on.