Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to 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" )
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.