Filtering Powerpivot

[CLExcel]

I am trying to filter a pivot table to display the very first record within a field. I am trying to get the first invoice number for a customer. As of now, I am getting multiple records, and have been unable to set some sort of filter to only get the first invoice. My data is organized in a tabular format, and the invoice number is a field in my main fact table.

In the chart below, for example, I would only want to see one InvoiceNumber for each STORE A, STORE B, and STORE C; it would be the oldest InvoiceNumber for the customer (so, it would be the first invoice for the customer).

Any thoughts on how to filter this data to get this result?

Customer Name (No.)	Sales Rep Name	InvoiceNumber	First Sale Date	OrderNumber	CustomerPO	Invoice Date
STORE A	TH	12345	1/1/13	0001	12	1/3/13
STORE A	TH	23456	1/1/13	0002	23	1/29/13
STORE B	LK	34567	2/7/14	0003	34	2/8/14
STORE B	LK	45678	2/7/14	0004	45	2/22/14
STORE B	LK	56789	2/7/14	0005	56	2/28/14
STORE C	RS	67890	3/8/14	0006	67	3/9/14
STORE C	RS	78901	3/8/14	0007	78	3/15/14

<tbody>
</tbody>

 

[PowerDAX]

Hi CLExcel....since this is in the Power Pivot forum, assuming you have the sales table in your model.

The easiest way to accomplish this is a calculated column on the sales table to flag the first invoice. The calculation below shows the first invoice for the custom across all dates. If you wanted it to be based on other static filters or selected filters, you would have to add other logic to the calculation (i.e. if within a particular year you would add a related(date[year]) entry to the ALLEXCEPT portion, or if you wanted it to be the first invoice within the other selected filters, you would utilize ALLSELECTED logic)

1. Added [First Customer Invoice] flag (TRUE/FALSE) on the Sales table:
=IF( [InvoiceNumber] = CALCULATE( MIN( Sales[InvoiceNumber]), ALLEXCEPT( Sales, Sales[Customer Name (No.)]) ), 1, 0 )
2. Added that as a slicer on the pivot table

PowerDAX.com

 

[CLExcel]

Your solution is just what was needed in this situation; major thanks, PowerDAX!