Adding A calculated Column to Determine the Last Month of the Year in Which a Sale Occurs

[JlmillerRedmond]

I have annual sales data pulled into an Excel Power Pivot and connected to a date table that includes a serial number for each month of the year (to eliminate the need for date math).  Data includes a line for each sale to a customer over the course of the year, which can mean more than one line per customer in a given month.

For each customer, I need to determine the last month during the year in which a customer made a purchase.  I'm currently using the following formula: 

=MAXX( FILTER( ALL(SalesTable), SalesTable[Customer] = Earlier(SalesTable[Customer] ,SalesTable[MoSerialNo])

NOTE:  there are other fields involved like product type that use the same parameter = earlier(parameter) form.

For customers with only one purchase in a month, this always produces the correct results.  But for customers with more than one purchase in a month, it produces two answers; the month in which the sale occurs and the last month in which a sale occurs.  This creates issues in extracting the data as there should be a single value for the last month in which a customer makes a purchase.

How can I either modify this formula, use another function, or create another field that will produce one answer (the maximum month serial number) for all instances of a customer that are in the sales data?

[jgeddes · Accepted solution]

You may be able to use something like this...

Calculated Column 

Last Month = 
var _cust = 
'Table'[Customer]
Return
MAXX(
    FILTER(ALL('Table'), [Customer] = _cust),
    'Table'[Month]
)

Result