cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulDe
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

 

 

 

4 REPLIES 4
maxigtsh
Helper I
Helper I

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

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.

PaulDe_0-1631166126788.png

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.

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 🙂 

 

Unbenannt.PNG

 

 

Regards

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!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!