Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Customer First Time Calculation Without Earlier

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.

 

DateCustomerSegmentTypeQuantityAmount
1.08.20124015882ASales170,57
1.09.20124015882ASales2227,12
1.11.20124015882AService12529,32
1.03.20134015882ASales-3-303,1
1.08.20184015882AService820,4
1.01.20204015882ASales4125,49
1.09.20204015882ASales262,47
1.08.20124017723BSales11189,31
1.03.20164017723BService291523,88
1.11.20124074720DSales1284,8
1.11.20124074720CSales3124,5
1.03.20134074720CSales1100,3
1.03.20134074720DSales845,2
1.08.20184074720DSales4125,49
1.08.20184074720DService14875,2
1.01.20204074720DSales215,2
1.09.20204074720DSales85

 

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

  • 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

     

     

     

3 Replies

  • 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's avatar
      Anonymous
      Not applicable

      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's avatar
        v-deddai1-msft
        Community Support

        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