Cannot Accumulate Values Over Multiple Years

Question

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

 

Answer 1

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

Answer 2

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.