New Distinct Count for the Next Day

[Likhitha]

Hello All,

I'm having 3 distinct customers on day-1 and for Day-2 a new customer is added .How to get that New Customer Count??

In the Image,ABC are new distinct  Customers for Day-1 so distinctcount is 3,For Day-2 D is the new customer and distinctcount should be 1.How to do that??

[v-angzheng-msft · Accepted solution]

Hi, @Likhitha 

Try to create 2 measures like below:

_New =
VAR _minDate =
    CALCULATE ( MIN ( [Date] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )
VAR _if =
    IF ( _minDate = MIN ( [Date] ), 1, 0 )
RETURN
    _if

_Count =
VAR _Count =
    SUMMARIZE ( 'Table', [Date], [Name], "_New", [_New] )
RETURN
    SUMX ( _Count, [_New] )

Sample:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Likhitha , Create measures like these with help from date table

This Day = CALCULATE(countrows(Table), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(countrows(Table), FILTER(ALL('Date'),'Date'[Date]<=max('Date'[Date])-1))

new customer = countx(values(Table[name]), if(isblank([Last Day]) && not(isblank([This Day])), [name], blank()))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series

Appreciate your Kudos.

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...