Forum Discussion
Helper IIINew vs Established Customers
I have two tables. One with customer entry to business and other one representing their sales. I want to calculate to daxes. One representing New and Established based on their created date column in table. Let me share the both table.
| locationID | dateCreated | ID |
| 102 | 17/06/2020 | 658800 |
| 102 | 29/10/2020 | 658803 |
| 102 | 09/12/2020 | 658806 |
| 102 | 29/03/2021 | 658809 |
| 102 | 17/06/2020 | 658797 |
| 103 | 17/06/2020 | 658798 |
| 103 | 16/06/2020 | 658795 |
| 103 | 02/07/2021 | 658812 |
| 103 | 11/06/2020 | 658792 |
| 103 | 02/12/2020 | 658804 |
| 103 | 22/06/2020 | 658801 |
| 103 | 16/06/2020 | 658796 |
| 104 | 12/04/2021 | 658810 |
| 104 | 11/06/2020 | 658793 |
| 104 | 22/12/2020 | 658807 |
| 104 | 17/06/2020 | 658799 |
| 104 | 11/06/2020 | 658794 |
| 106 | 17/05/2021 | 658811 |
| 106 | 07/12/2020 | 658805 |
| 106 | 22/02/2021 | 658808 |
| 106 | 13/10/2020 | 658802 |
| 106 | 24/02/2015 | 164931 |
The second table is :
| Client Id | Sales | Ticket ID | Start Date time |
| 102 | 2000 | 1233 | 11/2/2020 |
| 102 | 13000 | 1234 | 12/7/2020 |
| 103 | 29404 | 1244 | 21/11/2020 |
| 104 | 928 | 1255 | 29/12/2020 |
| 104 | 5421 | 5723 | 12/7/2020 |
| 106 | 2112 | 1245 | 19/4/2020 |
| 103 | 443 | 4562 | 02/8/2020 |
| 102 | 1234 | 1523 | 02/8/2020 |
| 106 | 974 | 4562 | 21/1/2020 |
I want to get two dax one for new and established customers and second one representing sales for both of the new and established. Kindly help.
Hi Hamdan1234 ,
According to your description, here’s my solution,
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Hamdan1234 , what is definition for that ?
refer in between
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497
Hamdan1234Hello amitchandak ,
I want to get answer in form of Two options Established or new for each Client ID. Is this possible?- amitchandak
Hamdan1234 , Like one of the way I can think of is first sales is New
New columns
Rank = Rankx(filter(Table, [client ID] = earlier([client Id]) ) , [Start Date time],,asc, dense)
Type = if([Rank] =1, "New", "Established")
Or we can based one for each customer
Flag =
var _cnt = countx(filter(Table, [client ID] = earlier([client Id]) , [Client Id] )
return
if(_cnt >=1 , "Established" , "New")
v-yanjiang-msftCommunity Support
Hi Hamdan1234 ,
According to your description, here’s my solution,
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
