Forum Discussion

AppleMan's avatar
AppleMan
Helper III
2 years ago
Solved

Calculating Previous Sales Date

I have an interesting issue I need help figuring out.   I have a table that is a list of orders (columns like order date, name, product, price, etc).  I have been requested to create a measure tha...
Need Help
  • kb_cc's avatar
    kb_cc
    2 years ago

    I would suggest the following DAX measure:

    New Customers - 3 Years =
    VAR N =
    CALCULATE(DISTINCTCOUNT(Orders[Customer ID]),
        FILTER (
            Orders,
            Orders[Order Date]
                = CALCULATE (
                    MIN ( Orders[Order Date] ),
                    ALLEXCEPT(Orders, Orders[Customer ID])
                )
            &&
            DATEDIFF(Orders[Order Date],TODAY(),YEAR)<3
        )
    )
    VAR R = DISTINCTCOUNT(Orders[Customer ID])-N
    RETURN
    N
    Simply return 'R' for returning customers.
AppleMan's avatar
AppleMan
Helper III
2 years ago

I will try this out. Looking at this code, variable N should be returning the number of customers that are not returning customers, correct?

So variable R will just find the number of distinct customers and then remove the ones that are not return customers?

kb_cc's avatar
kb_cc
Frequent Visitor
2 years ago

That's correct! Variable 'N' returns the number of customers who made their first purchase within the past 3 years.
Variable 'R' returns any customers that do not meet this criteria.

  • AppleMan's avatar
    AppleMan
    Helper III
    2 years ago

    I did some testing, disregard this message I understand what it is doing now. I do not have a way to verify if the numbers I am getting are correct, but the code makes sense and I will accept this as the answer. I appreciate it! 

    I can open a new request if I find out the data I am getting is inaccurate and I need help refining it. Thanks again!

    • kb_cc's avatar
      kb_cc
      Frequent Visitor
      2 years ago

      Try something like this:

      New Customers =
      CALCULATE(DISTINCTCOUNT(Orders[Customer ID]),
          FILTER (
              Orders,
              NOT(Orders[Customer ID] IN VALUES(Customers[Customer ID]))
              ||
              DATEDIFF(Orders[Order Date],TODAY(),YEAR)>3
          )
      )
       
      The "pipes" (||) equate to an "or" in DAX.
       
      Hope this helps!
    • kb_cc's avatar
      kb_cc
      Frequent Visitor
      2 years ago

      Happy to help! Please mark my reply as the solution so other users can source the answer if they have a similar query. Thanks!