Categorize customers depends on their order dates

[Anonymous]

Hi everyone, 

i hope someone can help me here.

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

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

[Anonymous]

Hello,

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 =

SWITCH(TRUE(),

days = 0, "Neukunde",

days > 0 && days <= 30, "Bestand",

days > 30, "Reaktiviert"

)

Return

CustomerType

Regards

[Anonymous]

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.

[Anonymous]

Hey,

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 🙂 

Regards

[Anonymous]

Hello,

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!