Calculating Sum with Power Pivot

[Seeking_Answers]

Hi,

Hoping someone can help with this!

I have two tables in a power pivot model; Sales and Dates (there are a lot more, but these are the two I want to use).

Sales Table:

Month​	Contract​	Item​	Sales​
January 2014​	10001​	400001​	150​
January 2014​	10001​	400002​	120​
January 2014​	10001​	400005​	87​
January 2014​	10001​	200001​	22​
January 2014​	10002​	200001​	21​
January 2014​	10003​	400005​	101​
January 2014​	10003​	400003​	320​
January 2014​	10004​	400002​	205​
February 2014​	10001​	400001​	362​
February 2014​	10003​	200001​	31​
February 2014​	10003​	200004​	304​

<tbody>
</tbody>

Dates table:

Month​	Rolling_Year_Start​
January 2014​	February 2013​
February 2014​	March 2013​
March 2014​	April 2013​
​

<tbody>
</tbody>

I want to insert a calculated column on the sales table to give me the rolling year total filtered to for the Item and Contract, i.e. February 2014 for Contract 10001 and Item 400001 would be 362+150, but the January value would just be 150).

I've tried to use

=calculate(sum([Sales]),filter(sales,[Month]>=related(Dates[Rolling_Year_Start])),filter(sales,[Month]<=related(Dates[Month])),filter(sales,[Contract]=[Contract]),filter(sales,[Item]=[Item]))

But that just returns the whole sum, which I assume is because I'm filtering within the table itself.

Would really appreciate any help that's on offer!

Thanks!

 

[Matt Allington]

I want to insert a calculated column on the sales table to give me the rolling year total

Please don't do that. Read my article about why here Calculated Columns vs Measures in DAX - Excelerator BI

then read my article about calendar tables. Power Pivot Calendar Tables - Excelerator BI

make sure you have a month iD column and then use the custom time intelligence pattern I describe. something like this (depending on your definition of rolling year

Total Sales FYTD:=CALCULATE(
[Total Sales],
Filter(all(Calendar),
Calendar[ID]<=max(Calendar[ID]) &&
Calendar[ID<=max(Calendar[ID])-11
)
)