I want to achieve:
Adding a Column- Customer Type with the below condition to define New and Existing Customer at any point of time.
Related columns can be-
Customer Age = (Transaction Date - Customer Since Date) / 365
if Customer Age =< 0.5 then it’s New Customer , ELSE is an Existing Customer
Q1. How can I add the below columns in Power Query level-
Customer Since Date column
Customer age
Customer Type column Customer Data.pbix (166.7 KB)
please see if the attached meets your need.
NOTE: since you did not provide a copy of the Excel file, I had to create my own, and that is what is used for my solution - table titled “Customer Data (copy)”
Three major steps to the solution:
find the earliest date by customer -
did this by grouping by customer name and returning the “all rows” data, and the MIN of the date column
I then expanded the “all rows” data (less the customer number which was already showing
doing the math -
first I got the days between the start date and the date column
next I did the division of ‘days between’ and 365
an if/then statement to get the customer type
Finally- some recommendations:
for your final solution include a full, robust date table, which is MARKED as a date table
with the above, also turn off the auto date table in ALL new models (Global options, Data Load section under Time Intelligence), this will help to reduce your dataset size
Consider putting your Customer Start value onto the customer table, NOT your transaction table
Customer Age and Customer Type could be moved to Power Query (see Customer and Data tables for this solution)
NOTE: Power Query solution shown in this example is not the best, just something quick to demonstrate the possibility. EDNA Solution - Customer Aging.pbix (432.3 KB)
