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
jjattwood
Frequent Visitor

Total New Customers - Distinct Values

Hi All,

 

I have a list of around 800 rows each with different columns of data (customer name, company name, fee, project name, date received etc)

 

I also have a slicer on my powerbi dashboard to filter the dates id like to view based on the date received.

 

If I set the period to 1 month, I'd like to know the number of new unique/distinct company names in this time period (new customers) when compared with the remaining data in that column.

 

For example, ABC is the only company that has featured in my rows for the first time in my excel sheet during the month of August 2021 (or whatever I set as the time comaprison on my slicer), so the card that I add the measure to should display 1.

 

How do I go about adding this as a measure?

 

Many thanks!

2 REPLIES 2
amitchandak
Super User
Super User

@jjattwood , with help from date table 

All measures

Customer this month = calculate([Sales],datesmts('Date'[Date]))


Customer before = calculate([Sales],filter(all('Date'), 'Date'[Date] < = eomonth(max('Date'[Date]),-1)))


Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Customer this month ]) && not(ISBLANK([Customer before ])) , 1,BLANK()))


New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Customer before ]) && not(ISBLANK([Customer this month ])) ,1,BLANK()))


Retained Customer This Month = if(not(ISBLANK([Customer this month ])) && not(ISBLANK([Customer before ])) , 1,BLANK())

 

 

you can use company in place of customer.

 

also refer

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...
#PowerBI #DAX #PowerBI5 #powerbiturns5 #kanerika #businessintelligence #powerplatform #bi #powerbidesktop #datatodiscoveryin5Min #datatodiscovery

Hi Amitchandak,

 

Not sure I understand.

 

I don't have a date column, the dates on my visualisations are based on the date of opporunity received in short form '19/08/21'

 

What is the meaning for sales? Is this a number or the count of the number of customers? Also what does datesmts do?

 

I have a splicer where I can set the timeframe so I'm not concerned about the date, I just want it to compare the current time period of the slicer with the whole excel sheet.

 

Thanks

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.