Power Pivot Filtering and Summing Problem

[Nick Lyon]

Hi,

I'm hoping his is a relatively straight forward one and I am just missing something obvious.

I have a data table that I want to add a column to showing the total number of units for similar transactions - effectively ending up with a column showing the units for that individual transaction and one next to it (calculated) showing total units for that product. I have been playing around with various different filtering and summing options but don't seem to be getting anywhere.

The data will look something like the below...
[TABLE="width: 671"]
<tbody>[TR]
[TD]PRODUCT[/TD]
[TD]RELEASE DATE[/TD]
[TD]RECALL DATE[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]PERIOD[/TD]
[TD]COUNTRY[/TD]
[TD]UNIT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/11/2012[/TD]
[TD]12/11/2012[/TD]
[TD]01/11/2012[/TD]
[TD]15/11/2012[/TD]
[TD]14[/TD]
[TD]UK[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/11/2012[/TD]
[TD]19/11/2012[/TD]
[TD]13/11/2012[/TD]
[TD]11/12/2012[/TD]
[TD]28[/TD]
[TD]UK[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]19/11/2012[/TD]
[TD]26/11/2012[/TD]
[TD]21/11/2012[/TD]
[TD]05/12/2012[/TD]
[TD]14[/TD]
[TD]UK[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]26/11/2012[/TD]
[TD]03/12/2012[/TD]
[TD]26/11/2012[/TD]
[TD]10/12/2012[/TD]
[TD]14[/TD]
[TD]USA[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03/12/2012[/TD]
[TD]10/12/2012[/TD]
[TD]26/11/2012[/TD]
[TD]24/12/2012[/TD]
[TD]28[/TD]
[TD]UK[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

So (taking the first row of data as an example) I require a column on the end of this that sums the total units in the whole data table that have a period of 14, in the UK, for product number 1. That much I can do, it is when I add in the next stage that things seem go wrong. Essentially, where the release date falls between the start and end dates that needs to be included to. Looking at the second line to elaborate on that a bit more, the calculated column would need to say that there are 3 units from that line that need to be added with 1 unit from he first line (as the end date of the first line is after the release date on he second line). Probably not explained it in the best way but hopefully that makes sense?

Basically I need to filter my table on the below and sum the units column.
- product (of current line)
- period (of current line)
- country (of current line)
- start date < recall date (of current line)
- end date >= release date (of current line)

..it is the last two that are causing the issues so any help would be greatly appreciated.

Thanks in advance for your help!
Nick

 

[MD610]

The logic in the explanation is a little hard to follow.

Based on the sample data you provided, what is the expected results for the values in each row that you would like to see?

 

[ruve1k]

To achieve what you describe you could use the formula below:

=CALCULATE(SUM([UNIT]),
           ALLEXCEPT(Table1,Table1[PRODUCT],Table1[PERIOD],Table1[COUNTRY]),
           Table1[START DATE]<EARLIER(Table1[RECALL DATE]),
           Table1[END DATE]>=EARLIER(Table1[RELEASE DATE]) 
   )

Regarding your example:

Looking at the second line to elaborate on that a bit more, the calculated column would need to say that there are 3 units from that line that need to be added with 1 unit from he first line

The second line has a different Period so I'm not sure why that row is relevant at all as your requirement clearly states that the Period needs to equal the Period on the current row.

Also, note that given your limited data set, the result of this column is identical to the original Unit column.

 

[MD610]

ruve1k, I think he is looking for an alternate condition that also sums if the release/recall dates from one line fall within another.

So calculate the sum if Product, Period, and Country are equal or calculate the sum if Product, County, and the Date Ranges coincide. At least that is how I interpret it based his line about adding the 3 units from line 2 to the 1 unit from line 1.

It is not completely clear, which is why I was hoping for Nick to provide specific expected results for the sample set.

Based on my interpretation, I think it will be:
[TABLE="width: 151"]
<tbody>[TR]
[TD]Column Result
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

Again, without more detail, I may be interpreting him wrong. I think it is confusing because the periods and dates seem to not be related. Period 14 has multiple ranges and the last 2 records have ranges that almost overlap but their periods are different.

This does that result on the sample data but it will probably be painfully slow if he has an enormous data set:
=CALCULATE(SUM([UNIT]), FILTER('Table1', ('Table1'[PRODUCT]=EARLIER('Table1'[PRODUCT]) && 'Table1'[PERIOD]=EARLIER('Table1'[PERIOD]) && 'Table1'[COUNTRY]=EARLIER('Table1'[COUNTRY])) || ('Table1'[PRODUCT]=EARLIER('Table1'[PRODUCT]) && 'Table1'[COUNTRY]=EARLIER('Table1'[COUNTRY]) && 'Table1'[START DATE] < EARLIER('Table1'[RECALL DATE]) && 'Table1'[END DATE] >=EARLIER('Table1'[RELEASE DATE]))))