powerbipowerquerymcustomcolumn

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:

sample data

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.

Solution

  • 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" )