New and Established Customer

[Hamdan1234]

I have two table with two dates. One table has createdate for customer coming for the first time. It has date for all customer with their IDS and first time they entered to business  The other table has sales of all the customers on different date from time to time. I want to get the column chart as shown below.  

I am attaching both the tables along with the criteria. 

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

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

The criteria is:

New Client is defined based on their Create Date (dateCreated) from first table.

Established Client is anyone who has a Create Date prior to respective year we are referring to

For example, if you were analyzing 2020, anyone with a Create Date in 2020 would be defined as a New Client and anyone with a Create Date in 2019 or earlier would be considered Established Client. I really need help with this. Please if anybody can help me out.

[Ashish_Mathur]

Hi,

Have you resolved this problem or do you still need help?

[v-shex-msft · Accepted solution]

Hi @Hamdan1234,

For your scenario,  you can create a calendar table with full dare records and use it as the axis of the chart. Then you can add a table with new and Established types and use it on your chart legend.

After these steps, you can write a measure expression to loop raw table records based on the current axis and legends.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Regards,
Xiaoxin Sheng