cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aidelsrkk Frequent Visitor
Frequent Visitor

Power Query Statements for New and Repeated Order (Custom Column)

I have a Sales table consists of Date, Customer and Product columns. I want to segregate the data into two parts, New and Renew order. Each customer can order many types of products and each new product ordered by the customer will be state as New and if the same customer order the same product days or months later, it will be state as Renew.

 

Here is my sample data and column that I want to create:image.png

 

So far I created a column by merging Customer and Product into one column to make it unique and got stuck to sort it by date. Or is there any other way that will make this easier? Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
aidelsrkk Frequent Visitor
Frequent Visitor

Re: Power Query Statements for New and Repeated Order (Custom Column)

I've found a way. Here's what I did. I combined Customer and Product so it became unique and created a calculated column like this:

 

New/Renew = 
VAR counta =
CALCULATE (
COUNTROWS ( 'Sales' ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[CustomerXProduct] ),
'Sales'[Date] < EARLIER ( 'Sales'[Date] )
)
)
RETURN
IF ( counta = 0, "New", "Renew" )

View solution in original post

1 REPLY 1
aidelsrkk Frequent Visitor
Frequent Visitor

Re: Power Query Statements for New and Repeated Order (Custom Column)

I've found a way. Here's what I did. I combined Customer and Product so it became unique and created a calculated column like this:

 

New/Renew = 
VAR counta =
CALCULATE (
COUNTROWS ( 'Sales' ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[CustomerXProduct] ),
'Sales'[Date] < EARLIER ( 'Sales'[Date] )
)
)
RETURN
IF ( counta = 0, "New", "Renew" )

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 207 members 2,402 guests
Please welcome our newest community members: