Dynamic New Customer count

[Anonymous]

Hi all, 

Small problem to you all. 

I have a sales fact table. Call it sales.

I have customer dimension data .  Call it customer_dim

I have a date table.  Call it date_dim. 

Sales fact:    Customer     Product Category      Sale_date   

                           A                            ZZ                  04/30/2019

                           A                            TT                   05/30/2019

My new customer calc is the following : 

new Customer = COUNTROWS(Filter(ADDCOLUMNS(values(Sales[customer]),"PreviousSales",

CALCULATE(DISTINCTCOUNT(Sales),

                    all(Product Category),Filter(ALL(DateTable),

                     DateTable[Fiscal Year]<min(DateTable[Fiscal Year])))),ISBLANK([PreviousSales])))

My issue is the following. 

This works fine at the year level however when I drill down to the Category level, this calculation give me within a fiscal year,  the same customer twice as a new customer in both  categories.   Because of the fiscal year Filter. 

Within a fiscal year I need to allocate the new customer count to the first sales or category which would be ZZ. 

I just cannot get my head around this.  I tried to work within the sales date (firstnonblank and lastnonblank) but I  do not have a proper result. 

Any ideas would be beneficial.

Thanks

Francois

P.S. The date table was created this way calendar(date(beginning),date(end date))

With fiscal year created manually 

====== Additional Info ======

OK, here's some additional info on my issue.   The fiscal year starts at 4/1/2019 and ends at 3/31/2020.  

A bit more data... 

Sales fact:    Customer     Product Category      Sale_date   

                           C                            TT                  03/15/2019

                           A                            ZZ                  04/30/2019

                           A                            TT                   05/30/2019

So customer A is a new customer since he never purchase anything before. 

However when I use the calc up in this message and I use product category, the customer A falls in both ZZ and TT product Category.  However I want the customer to fall only in the first purchase so ZZ being the first category.  If the customer purchase later I do not want to be part of the TT category as a new customer. 

I am trying to weed out the second category based on the following. 

If the new customer made a purchase within the current fiscal year, do not count it as a new customer in that category. 

It might be more confusing however thank you all to look into that. 

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur 

I know why the smile on your face.... 

This works great.  However since I am new to DAX let me see if I understand. 

They way you  think is that for every sales fact, you create a measure that would evaluate if this is a new customer (or first sales) or not. Then we only have to count the rows. 

So the calc is  simple.  We do a count on the customer number where the the date of first interaction is greater than the min date table during the current time frame selected, and the date of first  interaction = the date of a first interaction for any product. 

Now let me know if I understand the two basic measures. 

The date of first interaction is the min(sales Date) for all the date within the calendar.   However it might be dynamically filtered by product category. 

The date of first interaction for any product, is the minimum date for all product category. The  all(product category) ignores the filter.  

Your  solution works great.  However, if I add another type of category:

Date of first interaction for any product = CALCULATE(MIN(Sales[Sale_date]),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MAX('Calendar'[Date])),ALL(Sales[Product Category]),ALL(Sales[Product other_category])

It does not behave the same. I thought I could just add this new filter but it does not change the calculation on other_category.  

==============
Also, instead of talking nuts and bolts. I like the concept of  row level measures instead of aggregating at Fiscal year level. 

Is it customary to  think this way?  Are there any performance issue with this?  

Thanks

Francois

[Ashish_Mathur]

Hi,

You are welcome.  Your understanding is correct.  Your edited formula should work - I do not know why it isn't.  I will need your file with that column added and clearly show me in the visual that the result is wrong.

[v-lid-msft]

Hi @Anonymous ,

As the sample you shared, I think that you want to correct the total, you can change your measure to following:

new Customer = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DISTINCT ( Sales[Customer] ),
                "Previousdate", MINX ( FILTER ( ALL ( Sales ), Sales[Customer] = [Customer] ), [Sale_date] )
            ),
            "Category", LOOKUPVALUE ( Sales[Product Category], Sales[Sale_date], [Previousdate] )
        ),
        AND (
            YEAR ( [Previousdate] ) >= SELECTEDVALUE ( DateTable[Fiscal Year] ),
            IF (
                ISINSCOPE ( Sales[Product Category] ),
                [Category] = SELECTEDVALUE ( Sales[Product Category] ),
                TRUE ()
            )
        )
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Hi, 

I tried it but I am not really sure how this would fit in my problem. I have to do two things:

1) use my calc of new customer

2) Remove all those how purchased within the fiscal period prior to the sale date. 

OR

Change the calc 

I added a bit more info on the problem and I can put additional info if needed. 

Thanks

F

[v-lid-msft]

Hi @Anonymous ,

I apologize for posting answer of another post here  what may confuse you, I have updated the answer.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more