Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to 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
Proud to be a Super User! | |
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
Proud to be a Super User! | |