Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |