cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
flahaye Frequent Visitor
Frequent Visitor

Dynamic New Customer count

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic New Customer count

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
v-lid-msft Super Contributor
Super Contributor

Re: Dynamic New Customer count

Hi @flahaye ,

 

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 ()
            )
        )
    )
)

Dynamic-New-Customer-count-1.png

 

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

flahaye Frequent Visitor
Frequent Visitor

Re: Dynamic New Customer count

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 Super Contributor
Super Contributor

Re: Dynamic New Customer count

Hi @flahaye ,

 

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

Super User
Super User

Re: Dynamic New Customer count

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

flahaye Frequent Visitor
Frequent Visitor

Re: Dynamic New Customer count

@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
 
Super User
Super User

Re: Dynamic New Customer count

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 424 members 3,765 guests
Please welcome our newest community members: