Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Identify new Customers

Hi,

 

I have a list of sales by customer and by week. I'd like to identify how many new customers I have each week. In my database, I have data for last 3 years, howerver, I want to apply the formula only to the data that is after 12th week of this year.

Below is an example of the result data.

 

WeekCustomerNew?
12Customer1yes
12

Customer2

yes

13Customer3yes
13Customer4yes
13Customer1no
14

Customer2

no

14Customer3

no

14Customer4no
15

Customer5

yes

15Customer6yes
16Customer1no

 

Thanks.

Need Help
Show and Tell

4 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Check this code for a new column:

    let
    _customer = [Customer],
    _currentWeek = [Week]
    in
    if _currentWeek = List.Min(Table.SelectRows(#"Changed Type", each [Customer] = _customer and [Week] >= 12)[Week]) then "Yes" else "No"

     

     

    Just change the bold part for your last step name.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could also use DAX to create a new column.

    Column =
VAR a =
    RANKX (
        FILTER (
            'Table',
            'Table'[Customer] = EARLIER ( 'Table'[Customer] )
                && 'Table'[Week] >= 12
        ),
        'Table'[Week],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( a = 1, "yes", "no" )

    Here is the result.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-eachen-msft  Thanks.
      This works but it takes the full database (all 3 years). I'd like to apply this only for this year.
      I have applied this as a filter, but it still didn't work as expected.

      Below is an example of how the visual is filtered


      Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Works Perfectly. Thanks for the solution