topic Re: Determining if a new customer has had a sale in DAX Commands and Tips

Determining if a new customer has had a sale

BudMan512 — Thu, 20 Oct 2022 21:48:22 GMT

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.

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

Re: Determining if a new customer has had a sale

v-yueyunzh-msft — Fri, 21 Oct 2022 01:51:39 GMT

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

Re: Determining if a new customer has had a sale

BudMan512 — Fri, 21 Oct 2022 13:05:10 GMT

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.

Output

Re: Determining if a new customer has had a sale

BudMan512 — Fri, 21 Oct 2022 13:09:03 GMT

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.

Re: Determining if a new customer has had a sale

v-yueyunzh-msft — Mon, 24 Oct 2022 02:16:54 GMT

Hi , BudMan512

Here are the steps you can refer to :

(1)This is my test data :

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

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

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

Re: Determining if a new customer has had a sale

BudMan512 — Mon, 24 Oct 2022 14:30:06 GMT

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

Re: Determining if a new customer has had a sale

v-yueyunzh-msft — Tue, 25 Oct 2022 02:39:36 GMT

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:

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

Re: Determining if a new customer has had a sale

BudMan512 — Tue, 25 Oct 2022 13:10:14 GMT

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