Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |