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.
kb_cc's avatar
kb_cc
Frequent Visitor
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!