Customer First Time Calculation Without Earlier

[Anonymous]

Hi All,

First of all I'm sorry but this article is a little long but It really confusing thing for me I try to explain clearly
I'm in trouble with Earlier function. It is really significant function but it only works with calculated column and it get me into trouble.

Let's give an example.
I have a dataset like that.

Date	Customer	Segment	Type	Quantity	Amount
1.08.2012	4015882	A	Sales	1	70,57
1.09.2012	4015882	A	Sales	2	227,12
1.11.2012	4015882	A	Service	12	529,32
1.03.2013	4015882	A	Sales	-3	-303,1
1.08.2018	4015882	A	Service	8	20,4
1.01.2020	4015882	A	Sales	4	125,49
1.09.2020	4015882	A	Sales	2	62,47
1.08.2012	4017723	B	Sales	1	1189,31
1.03.2016	4017723	B	Service	29	1523,88
1.11.2012	4074720	D	Sales	12	84,8
1.11.2012	4074720	C	Sales	3	124,5
1.03.2013	4074720	C	Sales	1	100,3
1.03.2013	4074720	D	Sales	8	45,2
1.08.2018	4074720	D	Sales	4	125,49
1.08.2018	4074720	D	Service	14	875,2
1.01.2020	4074720	D	Sales	2	15,2
1.09.2020	4074720	D	Sales	8	5

I want to find 2 thing.

1- Customer's first coming date.
I was created a calculated column like that and filter only "1" values on my visuals.
 

First Time = CALCULATE (
    COUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ))

It is working well when customers have only 1 value for each month but It is not working well if customers have 2 values for 1 month.

2-Find the date if customers coming after 3 years.

3 Year Break = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )))
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
           'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[Date] >= EDATE ( EARLIER ( 'Table'[Date] ), -36 )
                && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )))
RETURN
    IF ( ISBLANK ( a ) || b > 0, 0, 1 )

But again it is not working well when there is 2 record for a month.
In Example:

As I understand doing this things on column is not working for me because my reqierment is dynamic and when we do that on column It became static.

The point I ask for help is that I want to create these calculations as Measure

Here is my PBIX file

[amitchandak]

@Anonymous , You can create a column like this

Last Date = maxx(filter('Table' ,[Date] < EARLIER ( [Date] ) && 'Table'[Customer] = EARLIER ( 'Table'[Customer])),'Table'[Date])

If this null then it first visits

Now a new column

datediff([Last Date ],[Date] ,DAY) // diff from last order

datediff([Last Date ],[Date] ,Month)

You can change such a column in measure with two changes

Last Date = maxx(filter(allselected('Table') ,[Date] < max( [Date] ) && 'Table'[Customer] = max( 'Table'[Customer])),'Table'[Date])

[Anonymous]

Hi @amitchandak ,

I tried your measure but it doesn't return the expected result.

I want to find customer count for each date. I shared the screenshot in the previous post.

I need something like that.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can change your [First Time] column to 

First Time = RANKX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer])),'Table'[Date],,ASC,Dense)

Use another measure for counting customer for 3 year break:

_3 Year Break = CALCULATE(DISTINCTCOUNT('Table'[Customer]),'Table'[3 Year Break] = 1)+0

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai