locked
DAX - Calculate New Customer RRS feed

  • Question

  • Hi All,

    I'm new in the dax world.

    I need to calculate the number of new customers of 2014 in my FactInvoice Table.

    How can i translate in DAX code the follow query SQL?

    select count(DISTINCT CustomerID)
    FROM Invoice
    where CustomerID not in (SELECT CustomerID from Invoice where year(DateID)<2014)

    Thank's!!!

    Friday, June 13, 2014 10:51 AM

Answers

  • Google is your friend :)

    https://www.google.at/webhp?q=dax+new+customers#q=dax+new+customers

    there are plenty of blog posts about that topic out there, just pick one

    I also wrote two blogs abouts it which can be found here:
    http://blog.gbrueckl.at/2013/02/another-post-about-calculating-new-and-returning-customers/
    http://blog.gbrueckl.at/2013/03/another-post-about-calculating-new-and-returning-customers-part-2/

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by AlessandroGG Friday, June 13, 2014 12:33 PM
    Friday, June 13, 2014 11:15 AM
    Answerer

All replies

  • Google is your friend :)

    https://www.google.at/webhp?q=dax+new+customers#q=dax+new+customers

    there are plenty of blog posts about that topic out there, just pick one

    I also wrote two blogs abouts it which can be found here:
    http://blog.gbrueckl.at/2013/02/another-post-about-calculating-new-and-returning-customers/
    http://blog.gbrueckl.at/2013/03/another-post-about-calculating-new-and-returning-customers-part-2/

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by AlessandroGG Friday, June 13, 2014 12:33 PM
    Friday, June 13, 2014 11:15 AM
    Answerer
  • hope date id contains datetime data.

    here is the query .

    Evaluate
    (
        Filter
        (
            Summarize
            (
                Summarize
                (
                    Invoice,[CustomerID],[DateID],
                    "Year", Year(Invoice[DateID])
                ),[CustomerKey],[Year]
            ),[Year]<2002
        )
    )

    Friday, June 13, 2014 12:03 PM
  • I also published an article on DAX Patterns web site that includes calculation for new, returning, lost and reactivated customers:

    http://www.daxpatterns.com/new-and-returning-customers/

    I thanks Gerhard for inspiring some of the Solutions implemented in the article!


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Saturday, June 28, 2014 3:50 PM