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

JlmillerRedmond — Thu, 16 Jan 2025 21:32:09 GMT

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?

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

jgeddes — Thu, 16 Jan 2025 21:47:31 GMT

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

topic Re: Adding A calculated Column to Determine the Last Month of the Year in Which a Sale Occurs in Power Query

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

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