Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
Solved! Go to 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:
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 , @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
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 :
(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
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
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
Thank you so much for your help. It is truly appreciated. Bud
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |