Count number of orders per customer in column

[JeroniJamboni]

Hi Guys,

I'm relatively new with dax formula's and trying to work my way through the data I have to become a real DAX-Master;). Bought the book and visit this website regularly, which is very helpfull! At the moment however i seem to have a lot of difficulties trying with finding returning customers. I know that there are a lot of posts out there, which propably know already, but i can't seem to find a solution to my question.

I have a calculated sales table with:
Orderdate, OrderID, ProductID, CustomerID, Orderstatus
(by the way: Multiple order lines per order ID)

I would like to know how i could get a calculated column which calculates the number of previous orders of this customers. I would really like to have it filtered to order state. Only orders with the "completed"-status should be counted. 

Hope my question is clear. I tried almost everything. I can't find a good solution. I hope you guys can be of any help.

Jeroen

[Anonymous]

HI @JeroniJamboni,

I think it will be help if you provide some sample data to analysis.

Below is the sample measure which I haven't test on real data, maybe you can try it if it suitable for your requirement:(create table visual with 'customer id' and below measure)

Order Count =
CALCULATE (
    DISTINCTCOUNT ( Table[OrderID] ),
    FILTER (
        ALL ( Table ),
        [CustomerID] = MAX ( Table[CustomerID] )
            && [Orderstatus] = "completed"
    )
)

Regards,

Xiaoxin Sheng

[JeroniJamboni · Accepted solution]

Hi,

Thanks for you answer. Works great to find the total number of orders, but i wanted to get the sequential number of the order. 

I found this solution, which works great!

Order sequence = 

COUNTROWS(
    FILTER(
        CALCULATETABLE(
            TableOrder;
            ALLEXCEPT(TableOrder;TableOrder[CustomerID])
        );
        TableOrder[Orderdate] < EARLIER(TableOrder[Orderdate])
        &&
        TableOrder[Orderstate] = "Complete"
    ))+1

By the way. The +1 is used to eliminate the zero's which it returns for first-time orders;).

Thanks anyway for your help. Got me thinking:D

Jeroni