Applying slicers to a lookup table

[cr731]

I have two tables:

1. Source data containing a list of projects and their status (open, closed ... and for closed, the date completed).

2. Table of months in the year 2013.

My end goal for my "table of months" table is something like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Count of Projects Completed[/TD]
[TD]Projects Open[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]12[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Where the Count of Projects Completed is basically like a SUMIF formula to the #1/source table based on the completion date. Projects Open is a little more complicated as it has to sum the number of projects that were created before the end of the month but not closed yet as of the end of the month.

But, I want to be able to put this into a pivot table and apply slicers that would go on the source table, for example, to be able to apply a slicer by the Customer Name, which is a column in my table # 1.

Can Powerpivot accomplish this? I can't just summarize using a standard pivot because of the complexity of the 'projects open' column, which could have overlap between months (i.e. a project open could be open in the Jan, Feb, and Mar lines all at the same time).

 

[scottsen]

Can Powerpivot accomplish this?

Yes.

If you setup a "relationship" (a powerpivot concept) between your tables, then slicing a lookup table, will also filter the data table.

 

[cr731]

Yes.

If you setup a "relationship" (a powerpivot concept) between your tables, then slicing a lookup table, will also filter the data table.

Understood. The problem is, I don't see any relationship between the two. My table of months is just that - a column of months and then the next columns are just those summing the number of projects completed, open, etc. The table of months doesn't share any of the other detail columns of my source data, like customer name, etc.

 

[scottsen]

Naturally, this is easier if I see more data (and typically I recommend making a "real" calendear/date table which is a sorta first class concept in powerpivot), but likely you are going to somehow relate Months (in your table of months) to Date Completed date, by creating a calc column that has the Month of the Date Completed date.