Forum Discussion

jjattwood's avatar
jjattwood
Frequent Visitor
4 years ago

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 t...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

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/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
#PowerBI #DAX #PowerBI5 #powerbiturns5 #kanerika #businessintelligence #powerplatform #bi #powerbidesktop #datatodiscoveryin5Min #datatodiscovery

  • jjattwood's avatar
    jjattwood
    Frequent Visitor
    4 years ago

    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