topic How to calculate days since each customer’s last purchase? in DAX Commands and Tips

How to calculate days since each customer’s last purchase?

Kavya_2001 — Wed, 04 Jun 2025 11:37:46 GMT

Hey everyone,

I’m working on a Power BI dashboard where I need to show how many days it's been since each customer last made a purchase.

The goal is to quickly identify which customers haven’t ordered in a while so the sales team can follow up accordingly. I already have the Customer and OrderDate fields in my dataset, but I'm quite struck on how to calculate the "days since last purchase" dynamically in a measure.

Has anyone implemented something like this before?
Ideally, I’d like to show this in a table with one row per customer and a column showing how many days have passed since their last transaction.

Appreciate any help or DAX suggestions!

Thanks in advance!

Re: How to calculate days since each customer’s last purchase?

DataNinja777 — Wed, 04 Jun 2025 11:42:20 GMT

Hi Kavya_2001 ,

To calculate how many days it's been since each customer's last purchase in Power BI, you can create a DAX measure using the DATEDIFF function. The idea is to find the most recent OrderDate for each customer, then calculate the number of days between that date and today. Assuming your table is called 'Sales' and it contains the columns 'Customer' and 'OrderDate', the following DAX measure will give you the result:

Days Since Last Purchase = DATEDIFF( CALCULATE(MAX('Sales'[OrderDate]), ALLEXCEPT('Sales', 'Sales'[Customer])), TODAY(), DAY )

This measure works by evaluating the latest order date for each customer using MAX, keeping the context filtered to each individual customer with ALLEXCEPT, and then computing the day difference between that date and the current date. You can place this measure in a table visual alongside the customer name to see how many days have passed since their most recent transaction.

Best regards,

Re: How to calculate days since each customer’s last purchase?

burakkaragoz — Tue, 01 Jul 2025 09:14:51 GMT

Hi Kavya_2001 ,

Just dropping in to say the answer above is totally solid and covers what you need. You can calculate the days since last purchase with that DAX setup, and it’ll work perfectly for a table visual with one row per customer.

Quick tip though—sometimes if you have multiple sales tables or more complex data models (like inactive relationships, or you want to ignore some extra filters), you might need to tweak the CALCULATE part. But for a standard setup, the formula shared should do the trick just fine.

If you run into any weird results or edge cases (like customers with no purchases at all showing up as blank or with weird numbers), let me know and I can help with some workarounds or extra tweaks.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

Re: How to calculate days since each customer’s last purchase?

Abhilash_P — Wed, 04 Jun 2025 14:46:12 GMT

Hi Kavya_2001

Something like below data funtions might resolve your issue i think
Days Since Last Purchase =
DATEDIFF(
    CALCULATE(MAX('SalesData'[OrderDate]), ALLEXCEPT('SalesData', 'SalesData'[Customer])),
    TODAY(),
    DAY
)

Thanks