Forum Discussion

Likhitha's avatar
Likhitha
Icon for Helper IV rankHelper IV
5 years ago
Solved

New Distinct Count for the Next Day

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 disti...
Need Help
Show and Tell
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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/ba-p/1361529