Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Categorize customers depends on their order dates

Hi everyone, 


i hope someone can help me here.

Here is my Table with existing Values (Blue Headers) and missing Values (yellow Headers)

2021-09-07 21_24_21-Test Kundendaten - Excel.jpg


In column D i need the date of the second to last order from each KndNr, if this was the first order than i need the order date. I highlighted the dates in different colours for a better view. Now i have to calculate the duration between this dates (Column E) and based on the values i have to categorize the customer. 
In the end i only need the columns, KndNr, OrderID, OrderDate and the calculated CustomerType and im pretty sure that this new column can be done with one DAX formula. All the other columns are there to explain the way i have to make to call the customer as i need.


I hope someone can help me with my task.

Thanks Paul




Helper I
Helper I



if I understood it right, this should do the trick:


CustomerType =
var days = CALCULATE(DATEDIFF(MAX(SampleData[OrderDate]),MAX(SampleData[Earlier Order Date]),DAY) *-1)
var CustomerType =
days = 0, "Neukunde",
days > 0 && days <= 30, "Bestand",
days > 30, "Reaktiviert"

Hello maxigtsh, 


thanks for your help but something is not working, as you can see i can't use the EARLIER funktion in this case. I think this is the right way but syntax is not correct written.


The other thin is as i understand the code, there is no realtionship to the customer ID, but i need the new column based on each customer ID.
Now is have the original PowerBI screenshot i'm working with an in the column "Kundentyp soll" are the expected values i need in the new new column.



it is not working because there is missing the column EarlierOrderDate. How or from where do you get this data? If you can insert it into this table the measure works, see screenshot below 🙂 








i don't have the column "earlier date" this column has to be calculated also.
However, i tried yesterday a variation of your solution and it worked for a small data amount. But as i adapted this way to the original data table i was run out of memory. So i think the solution with variables is working fine if you have a relative small amount of rows, if you have to handle with millions of rows it isn't working. 

I reached my goal with powerquery, with indexes, joins and calculated columns and it is working now. The refresh needs at least 20 minutes but i sceduled this task to the night, so i have my needed values every morning.

Thanks anyway, i learned a lot from your solution!

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors