skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • New and Returning Customers

    New and Returning Customers

    05-02-2017 22:15 PM - last edited 05-02-2017 22:16 PM

    marcorusso
    MVP
    54243 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    marcorusso
    marcorusso
    MVP
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    New and Returning Customers

    ‎05-02-2017 10:15 PM

    Name:

    New customers

    Description:

    Calculate a measure filtering only the new customers in the selected period 

    Parameters:

    Name: Base value

    Tooltip: The value you want to calculate

    Type: Numerical field / measure

     

    Name:FactTable

    Tooltip: The table containing the data used in Base value

    Type: Table

     

    Name: DateOfTransaction

    Tooltip: The column containing the date of the transaction in the fact table (see FactTable)

    Type: Column

     

    Name: DateColumn

    Tooltip: The column Date in the Date table

    Type: Column

     

    Name: CustomerKeyColumn

    Tooltip: The column containing the customer identified in the customers table

    Type: Column

     

    {Base value} New Customers =
            VAR FirstPurchaseCustomers =
                ADDCOLUMNS (
                    ALL ( {CustomerKeyColumn} ),
                    "DateOfFirstBuy", CALCULATE ( MIN ( {DateTransaction} ), ALLEXCEPT ( {FactTable}, {CustomerKeyColumn} ) )
                )
            VAR NewCustomers =
                FILTER (
                    FirstPurchaseCustomers,
                    CONTAINS ( VALUES ( {DateColumn} ), {DateColumn}, [DateOfFirstBuy] )
                )
            RETURN
                CALCULATE (
                    {Base value},
                    NewCustomers 
                )

     

     

    Example:

    Sales New Customers =
            VAR FirstPurchaseCustomers =
                ADDCOLUMNS (
                    ALL ( Sales[CustomerKey] ),
                    "DateOfFirstBuy", CALCULATE ( MIN ( Sales[Order Date] ), ALLEXCEPT ( Sales, Sales[CustomerKey] ) )
                )
            VAR NewCustomers =
                FILTER (
                    FirstPurchaseCustomers,
                    CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] )
                )
            RETURN
                CALCULATE ( [Sales Amount], NewCustomers )

     

    eyJrIjoiODhiNTg0YzAtNmU5MC00YTBjLTlmODUtZmY5ZTJkYTE0MjcxIiwidCI6ImY1NDViZDY2LTdjM2YtNDcyOS04NTFhLWI3Y2EzYWM5ZmI2ZSIsImMiOjh9

    Preview file
    102 KB
    New Customers 20170502.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 15
    54,243 Views
    16
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-20-2018 01:42 PM

    This measure was returning more new customers than total customers until I modified this part of it: 

     

    VAR NewCustomers =
    CALCULATE(
      [Customers],
      FILTER (
        FirstPurchaseCustomers,
        CONTAINS ( VALUES ( 'Date'[DateKey] ), 'Date'[DateKey], [DateOfFirstBuy] )
       )
    )
    RETURN
      NewCustomers

     

    This now seems to be returning the correct values for me. 

    Message 13 of 15
    23,310 Views
    0
    Reply
    marcorusso
    marcorusso
    MVP
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-22-2018 01:30 PM

    That column name should be the column used for the relationship. You are probably using DateKey instead of Date.

    Message 14 of 15
    23,305 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to marcorusso
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-22-2018 01:40 PM

    Im wondering what would you do if I want to consider New Customers to be New customer only if they dont appear in previous year only? For example 2016= Customer, 2017=Not a customer then return 2018 then I want to count this customer in 2018 as well as in 2016. How may i acheive that?


    Thank you for your advice @marcorusso

    Message 15 of 15
    23,222 Views
    0
    Reply
    kirko
    kirko
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-26-2018 10:05 PM

    Hi

     

    What would the measure be for existing customers? or lost customers? this solves for the exact problem I am trying to solve!

     

    thanks

    Message 12 of 15
    23,402 Views
    0
    Reply
    belmore
    belmore Helper II
    Helper II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-19-2017 10:44 AM

    Thank you for your help.  I do not understand why the yearly subtotals do not sum correctly.

     

    For example: Year 2007 

     

    The martix shows the following Sums for 2007

    Customers |  New Customers  | Returning Customers

    -1409            -1409                  -0

     

    It seems like the Sums should equal the following

    Customers |  New Customers  | Returning Customers

    -1498            -1409                  -89

     

    Thank you for your help.

     

    Message 9 of 15
    52,194 Views
    3
    Reply
    lcasey
    lcasey Post Prodigy
    Post Prodigy
    In response to belmore
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-06-2017 04:01 PM

    I agree,

     

    The sums are wrong and make no sense.  This calculation should never be used in real world scenarios. 

     

    Hey, If I calculate 1 +1 it equals 5

     

    Thats exactlly what you are teaching here...:)

     

     

     

     

    Message 10 of 15
    51,864 Views
    0
    Reply
    jason435
    jason435 Helper II
    Helper II
    In response to lcasey
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-02-2018 03:32 AM

    I thought that was an error at first as well.

    It depends on how you interpret the meaning of subtotal. Once you think about it, the only way that makes sense is what Marco did.

    The subtotal row is filtered for the entire period above it. 2007 was the first year, so all customers are new customers in 2007 by definition. If the return customers subtotal was the total of each month's returning customers, new plus returning customers would be more than total customers. That wouldn't make sense.

     

    So would the student @lcaseylike to take over and show the class a better solution? Smiley Wink

    Message 11 of 15
    23,558 Views
    0
    Reply
    CAPEconsulting
    CAPEconsulting Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-14-2017 10:14 PM

    Hey @marcorusso,

     

    Would this approach work fine - so without VARIABLE function and also a bit smaller code

     

    New Clients = COUNTROWS (FILTER (CALCULATETABLE (ADDCOLUMNS (VALUES ( Episodes[client_key] ),"DateOfFirstService", CALCULATE ( MIN ( 'Service Contacts'[Service Date] ) )), ALL ( 'Service Contacts'[Service Date] )), CONTAINS (VALUES ( 'Service Contacts'[Service Date] ), 'Service Contacts'[Service Date], [DateOfFirstService] )))

     

    Your fan Smiley Happy,

    Abhijeet

    Message 7 of 15
    52,340 Views
    0
    Reply
    marcorusso
    marcorusso
    MVP
    In response to CAPEconsulting
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-15-2017 03:04 AM

    If you need to use older versions of DAX, avoiding variables makes sense. Otherwise, smaller code doesn't mean neither simpler nor faster code. Please remind that variables can help the engine to get more optimized query plans, avoiding to evaluate the same expression multiple times.

    It is not necessarily the case for this formula, but it could be in a complex query and in general it is a best practice.

     

    Marco Russo - SQLBI

    Message 8 of 15
    52,329 Views
    0
    Reply
    belmore
    belmore Helper II
    Helper II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-09-2017 02:42 PM

    The Grand totals for the [Sales New Customers] column equal the Grand totals for the [Sales Amount].....what can I do to get the GrandTotals for [Sales New Customers] to equal -- the actual GrandTotals for the column? Thanks

    Message 6 of 15
    52,463 Views
    0
    Reply
    tringuyenminh92
    tringuyenminh92 Memorable Member
    Memorable Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-05-2017 11:46 PM

    Hi @marcorusso,

     

    i dont understand meaning of  CONTAINS ( VALUES ( 'Date'[Date] ), 'Date'[Date], [DateOfFirstBuy] ), could you describe the underlying of it?

    Message 3 of 15
    52,721 Views
    0
    Reply
    marcorusso
    marcorusso
    MVP
    In response to tringuyenminh92
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-06-2017 12:57 AM

    It checks whether the [DateOfFirstBuy] is in the list of dates included in the current filter context.

     

    Marco Russo - SQLBI

    Message 4 of 15
    52,692 Views
    1
    Reply
    Anonymous
    Not applicable
    In response to marcorusso
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-06-2017 08:21 AM

    The addition of the quick measure into the October release brought me to your version... 

    What I would like to do is see a to-date or year to-date view of how the new sales compares to all sales but my attempts so far have floundered. i.e. a running total of new sales 

    I might also want to see how many sales we've made for new customers in year i.e. customer is seen as new from a certain date to another date rather than just a point in time.  If I drill up to years I know I can get that figure but not see a trend by period. 

    Message 5 of 15
    52,637 Views
    1
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-29-2017 08:37 AM

    Great Measure, thnx!

     

    Is it possible to make it so that filters can also be applied?

    Message 2 of 15
    53,827 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices