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 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!