Forum Discussion
New and Return Customer States - Power Query
Hi All,
I have a bit of a problem that I have been stuck with for a week or two and I was wondering whether you guys could help out.
I would like to transform this list:
| Customer | Year |
| A | 2015 |
| A | 2016 |
| A | 2017 |
| A | 2018 |
| A | 2019 |
| A | 2020 |
| B | 2015 |
| B | 2019 |
| B | 2020 |
| C | 2015 |
| D | 2015 |
| D | 2019 |
| D | 2020 |
| E | 2019 |
To look like the following matrix
| Customer | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 |
| A | New Customer | Active | Active | Active | Active | Active |
| B | New Customer | Active | Lapsed | Lapsed | Active | Active |
| C | New Customer | Active | Lapsed | Lapsed | Non-Customer | Non-Customer |
| D | New Customer | Active | Lapsed | Lapsed | Returnee | Active |
| E | New Customer | Active |
The rules are as follows:
New Customer - first purchase
Active - 0-2 years after last purchase
Lapsed - 2-4 years after last purchase
Non-Customer - 4 years after last purchase
Returnee - A return customer
I've tried looking at the New and Returning dax pattern but I haven't been able to work in the states that we require. Which is why I started to take a step back and look at power query. Any advice?
Ola
OAkanbi I don't know the Power Query way to do it, but this DAX table works:
Table2 = VAR __Customers = DISTINCT('Table'[Customer]) VAR __Years = GENERATESERIES(MIN('Table'[Year]),MAX('Table'[Year]),1) VAR __Table = GENERATE(__Customers,__Years) VAR __Table1 = ADDCOLUMNS( SELECTCOLUMNS(__Table,"__Customer",[Customer],"__Year",[Value]), "Status", VAR __MinYear = MAXX(FILTER('Table','Table'[Customer] = [__Customer] && [Year] <= [__Year]),[Year]) VAR __MinYear1 = MAXX(FILTER('Table','Table'[Customer] = [__Customer] && [Year] < [__Year]),[Year]) VAR __Diff = [__Year] - __MinYear VAR __Diff1 = [__Year] - __MinYear1 RETURN SWITCH(TRUE(), __Diff <= 2 && ISBLANK(__MinYear1), "New Customer", __Diff1 > 2 && __Diff = 0,"Returnee", __Diff < 2, "Active", __Diff >=2 && __Diff <4,"Lapsed", __Diff >= 4, "Non-Customer" ) ) RETURN __Table1
4 Replies
OAkanbi I don't know the Power Query way to do it, but this DAX table works:
Table2 = VAR __Customers = DISTINCT('Table'[Customer]) VAR __Years = GENERATESERIES(MIN('Table'[Year]),MAX('Table'[Year]),1) VAR __Table = GENERATE(__Customers,__Years) VAR __Table1 = ADDCOLUMNS( SELECTCOLUMNS(__Table,"__Customer",[Customer],"__Year",[Value]), "Status", VAR __MinYear = MAXX(FILTER('Table','Table'[Customer] = [__Customer] && [Year] <= [__Year]),[Year]) VAR __MinYear1 = MAXX(FILTER('Table','Table'[Customer] = [__Customer] && [Year] < [__Year]),[Year]) VAR __Diff = [__Year] - __MinYear VAR __Diff1 = [__Year] - __MinYear1 RETURN SWITCH(TRUE(), __Diff <= 2 && ISBLANK(__MinYear1), "New Customer", __Diff1 > 2 && __Diff = 0,"Returnee", __Diff < 2, "Active", __Diff >=2 && __Diff <4,"Lapsed", __Diff >= 4, "Non-Customer" ) ) RETURN __Table1Thanks very much. Does exactly what I was looking for. Brilliant work.
Why do B and D have different values in 2019?
Human error.
