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
BudMan512
Helper V
Helper V

Determining if a new customer has had a sale

 

Hi All,

I am creating a report of customers gained and lost.  Part of the project is for me to determine how many of the customers that have been added have never had a sale since being added as a customer.

BudMan512_0-1666300917051.png

I  only have a date table and a Customer table.  The active relationship is with the Start_Date. The End_Date and Last_Sale_Date are inactive relationships.

I also have a date Slicer. 

This is what I have used for Gain and Loss DAX

 

Gain = DISTINCTCOUNT(Customer[BR_CUSTID])

Loss = CALCULATE(Customer[Gain],USERELATIONSHIP('Date'[Date],Customer[END_DATE]))
 
I have a table below with multiple branches and their customer gains and losses so far in October 2022.
I would like two things if you should be so kind.
1. A count of how many of the customers gained so far do not have a Last_Sale_Date. I need the Last_Sale_Date to look beyond the date the customer was added.  That is if I look at January's gain/loss how many have not had a sale since their Start_Date?
2. I would like to make a list of the ones that have not had a Last_Sale_Date.
Could someone help me out with the DAX?  I am learning as fast as  I can but I'm not there  yet. I'm  71 so not sure I'll ever get there. ha,ha
Thanks much,
Bud
 

BudMan512_2-1666301401182.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi , @BudMan512 

You can click "New Table" in Power BI Desktop and enter this dax:

Table = SELECTCOLUMNS( FILTER('Customer','Customer'[START_DATE] <> BLANK() && 'Customer'[LAST_SALES_DATE] = BLANK()) , "BR_NAME" , [BR_NAME] , "NAME" , [NAME])

Then we can meet your need m the result is as follows:

vyueyunzhmsft_0-1666665537772.png

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

7 REPLIES 7
v-yueyunzh-msft
Community Support
Community Support

Hi , @BudMan512 

Based on your description, I have several doubts:

(1)In you 'Customer' table , Which one is the only flag? And whether the fields of this table are related, can you provide sample data for this table?

(2)In what form you ultimately want the result to be demonstrated?In a table or just in a card?

(3)For your first requirement, I don't quite understand the final desired result, can you provide us with the sample data you want to output in the form of a table?

 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thanks for the reply Aniya.

1. Each Customer has a Start Date field, an End Date field and a Last Sale Date field.  When a new customer is added the Start Date Field is populated with the date.  The End Date is populated when a customer is lost.  Most customers do not have an End Date.  The Last Sale Date indicates when the customer last made a purchase.  Sometimes customers are setup with a Start Date but they never make a purchase and so should not really be counted  as a Gain.  I am trying to identify those.  So this is not in DAX terms but I want to identify customers where Start Date is not null and Last Sale Date is Null.

2.  I would like to add a column to my table (screenshot above) with a count of the customers added that do have a Start Date but do not have an end date.  I also need a list of those customers in a table.

3. 

Output 

BudMan512_0-1666356448352.pngBudMan512_1-1666357304074.png

 

Sorry, in #2, I need to make a correction, it should say:

 I would like to add a column to my table (screenshot above) with a count of the customers added that do have a Start Date but do not have a Last Sale Date.  I also need a list of those customers in a table.

Hi , @BudMan512 

Here are the steps you can refer to :

(1)This is my test data :

vyueyunzhmsft_0-1666577686755.png

(2)I create a calendae table like yours and i create a relationship between two tables:

vyueyunzhmsft_1-1666577718973.png

(3)We can create two measures :

Gained with no Last Saled Date = var _t =FILTER('Customer' , 'Customer'[START_DATE] <> BLANK()  && 'Customer'[LAST_SALES_DATE] =BLANK())
return 
COUNTROWS(_t)
Customer Names = var _t =FILTER('Customer' , 'Customer'[START_DATE] <> BLANK()  && 'Customer'[LAST_SALES_DATE] =BLANK())
return 
CONCATENATEX( _t , [NAME] , ",")

(4) Then we can put the 'Date'[Date] in the slice and the fields we need on the visual and we will meet your need, the result is as follows :

vyueyunzhmsft_2-1666577791928.png

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Ania,

Thank you for all your hard work.  I just have one remaining change, sorry.

For the names of customers with no Last Sale date, I would like to have a separate Table Visualization as below.  To this I will add their start date and Last Sale Date of Blank.

I appreciate your help.  Thank You. - Bud

 

BudMan512_0-1666621658197.png

 

Hi , @BudMan512 

You can click "New Table" in Power BI Desktop and enter this dax:

Table = SELECTCOLUMNS( FILTER('Customer','Customer'[START_DATE] <> BLANK() && 'Customer'[LAST_SALES_DATE] = BLANK()) , "BR_NAME" , [BR_NAME] , "NAME" , [NAME])

Then we can meet your need m the result is as follows:

vyueyunzhmsft_0-1666665537772.png

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Thank you so much for your help.  It is truly appreciated.  Bud

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.

Top Solution Authors