Cannot Accumulate Values Over Multiple Years

# Cannot Accumulate Values Over Multiple Years

• Friday, September 10, 2010 12:11 AM

I am attempting to create a PowerPivot Chart that accumulates costs from one month to the next where the data spans multiple years.  I have two columns of data in my source, Date and Cost.  The dates are formatted as 1/1/2010, 2/1/2010, 3/1/2010, etc. through 6/1/2012.  My cost values are monthly costs with a cost value for each date value (i.e. 1/1/2010, \$12.00; 2/1/2010, \$12.00; 3/1/2010, \$12.00).  I need the chart to display these as \$12.00 for 1/1/2010, \$24.00 for 2/1/2010, \$36.00 for 3/1/2010, etc. (current month cost plus the previous months cost).

I am able to accomplish this for one year (2010) using a measure with the formula: =TOTALYTD(SUM('S-Curve Raw Data1'[BCWS]),'S-Curve Raw Data1'[Date], ALL('S-Curve Raw Data1')).

I am able to accomplish this for two years (2010 and 2011) using a measure with the formula: =TOTALYTD(SUM('S-Curve Raw Data1'[BCWS]),'S-Curve Raw Data1'[Date], ALL('S-Curve Raw Data1')) + CALCULATE(SUM('S-Curve Raw Data1'[BCWS]), PREVIOUSYEAR('S-Curve Raw Data1'[Date]), ALL('S-Curve Raw Data1')).

I believe that the measure than I am using for 2010 and 2011 is probably a hack and would like some help creating a measure that correctly accumulates the data for all three years.

Thanks!

Jon Lankford

### All Replies

• Saturday, September 11, 2010 9:07 AM

Hi Jon,

This should do the trick:

=if(sum(FactSales[SalesAmount]) > 0,
Calculate(sum(FactSales[SalesAmount]),
DATESBETWEEN(DimDate[Datekey],FIRSTDATE(all(FactSales[DateKey])), LASTDATE(FactSales[DateKey])))
,Blank())

This will calculate the sum of all dates from the firstdate there are sales (use all to get all the dates) to the lastdate in the current context.

Kasper
• Proposed As Answer by Saturday, September 11, 2010 9:28 AM
•
• Saturday, September 11, 2010 4:41 PM

By the way check out this blog post on the PowerPivot time intelligent function golden rules: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rules good to keep these rules when working with Time intel functions.