Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fitin1rb
Helper III
Helper III

new customers

Hello -

 

I am trying to calculate a New Customer Analysis.  I have 3 years worth of data and I am trying to determine which customers are new in 2017 (no transactions in 2015-2016).

 

I have a Customers Table (joined on Cust_ID) and a Date Table (Joined on Transaction Date) that are both joined to my Transaction table and below are a few sample transactions in my data.

 

Cust_IDTransaction_DateAmount
11/1/2015   100.00
25/1/2015   100.00
35/1/2016   100.00
410/1/2016   100.00
11/1/2017   100.00
45/1/2017   100.00
57/1/2017   100.00
68/1/2017   100.00
79/1/2017   100.00

 

So in this example I am expecting to see 2017 New Customers = 3 for a total Amount of $300.

 

Is there a measure that I can use in Order to pull this information?

 

Thanks
Ryan

1 ACCEPTED SOLUTION
Fitin1rb
Helper III
Helper III

@Zubair_Muhammad the work around that I am using is to set a Page Filter for the Cust_ID from the Calculated Table and include all Cust_ID <> blank and this seems to solve my issue for the time being.

View solution in original post

13 REPLIES 13
Fitin1rb
Helper III
Helper III

@Zubair_Muhammad the work around that I am using is to set a Page Filter for the Cust_ID from the Calculated Table and include all Cust_ID <> blank and this seems to solve my issue for the time being.

@Fitin1rb

 

So you also got a Blank Customer ID when you created the NEW Customers (Calculated Table)?

 

Wish you all the best with your project


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad I did, it was for everything that did not fall into the 2017 New Customers.  It seems to be working right now, so we shall see.  Thanks for your help!

Fitin1rb
Helper III
Helper III

@Zubair_Muhammad thanks for the reply.  Creating a table worked to get the #'s I was looking for, however, when I try to join it to my transaction table, so that I can see what customer name, industries, city/state etc our new business came from in 2017, it has every single value the same across the board unless I keep Cust_ID in there.  Is there anyway to change that?

 

As for the other suggestions, my transaction data set is well over 1 million rows of data, would creating a calculated column like that on that large of a data set cause any performance issues?

 

Thanks
Ryan

@Fitin1rb

 

As a Measure, try this

 

New or Old_MEASURE =
IF (
    YEAR ( SELECTEDVALUE ( Transactions_Table[Transaction_Date] ) ) = 2017,
    IF (
        CALCULATE (
            COUNTROWS ( Transactions_Table ),
            FILTER (
                ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ),
                YEAR ( Transactions_Table[Transaction_Date] ) < 2017
            )
        )
            >= 1,
        "Old",
        "New"
    )
)

18.png


Regards
Zubair

Please try my custom visuals

Hi @Fitin1rb

 

Were you able to create a relationship between CALCULATED TABLE (of new customers) and Customers Information Table?

 

If relationship is established, it should be easy to view more information about these new customers


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad I have created a relationship between the Calculated Table (New Customers) and the Customer Info Dimensions table (which is joined to my Transaction Table by Cust_ID) on the Cust_ID.  This is the type of results that I am seeing.

Power Bi.png

@Fitin1rb

 

Could you share the File?

 

A small question?

The sales field in the above Table Visual... is it coming from Transactions Table or the Calculated Table?


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad Unfortunately I cannot share the file.  I am using the SALES amount from the calculated table of new customers.

@Fitin1rb

 

Another simple technique or Workaround could be to add calculated column in the "Calculated Table (of New Customers)" to add Relevant information such as Industry or Segment using LOOKUPVALUE or RELATED or RELATED TABLE functions in DAX


Regards
Zubair

Please try my custom visuals
jthomson
Solution Sage
Solution Sage

The approach I'd take is to look to use the MIN function and return the earliest date from the transaction date, and use that as a way to filter your data table. I'm not sure whether you could use this all in measures or whether you'd have to make a calculated column in your customer table

Hi @Fitin1rb

 

One way of doing it

 

Go to Modelling tab>>>select the "New Table" button

 

New Customers Table =
VAR customersIn2017 =
    CALCULATETABLE (
        VALUES ( Transactions_Table[Cust_ID] ),
        YEAR ( Transactions_Table[Transaction_Date] ) = 2017
    )
VAR customersbefore2017 =
    CALCULATETABLE (
        VALUES ( Transactions_Table[Cust_ID] ),
        YEAR ( Transactions_Table[Transaction_Date] ) <> 2017
    )
RETURN
    SUMMARIZE (
        EXCEPT ( customersIn2017, customersbefore2017 ),
        Transactions_Table[Cust_ID],
        "Sales", CALCULATE (
            SUM ( Transactions_Table[Amount] ),
            YEAR ( Transactions_Table[Transaction_Date] ) = 2017
        )
    )

15.png


Regards
Zubair

Please try my custom visuals

Hi @Fitin1rb

 

Another way of doing it is to Add a Calculated Column in the Transactions Table identifying customers as Old or New

 

New or Old =
IF (
    YEAR ( Transactions_Table[Transaction_Date] ) = 2017,
    IF (
        CALCULATE (
            COUNTROWS ( Transactions_Table ),
            FILTER (
                ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ),
                YEAR ( Transactions_Table[Transaction_Date] ) < 2017
            )
        )
            >= 1,
        "Old",
        "New"
    )
)

 

16.png

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.