Count number of transactions per customer status

[Anonymous]

Hi guys, 

I have an issue with my formulas in order to calculate the number of transactions each month per customer status.

I have three tables : Customer, Order and Calendar 

My order table looks like this 

Sales order date	transaction ID	Customer ID	Amount
01/10/2019	xxxx	xxxx@gmail.com	120
02/10/2019	xxxx	xxxx@gmail.com	121
03/10/2019	xxxx	xxxx@gmail.com	122
04/10/2019	xxxx	xxxx@gmail.com	123
05/10/2019	xxxx	xxxx@gmail.com	124
06/10/2019	xxxx	xxxx@gmail.com	125
07/10/2019	xxxx	xxxx@gmail.com	126
08/10/2019	xxxx	xxxx@gmail.com	127
09/10/2019	xxxx	xxxx@gmail.com	128

i tried some of the formulas in this forum but i still don't get the right number: 

Stay Count New Customers =

COUNTROWS(

FILTER(

SUMMARIZE(

Orders,Orders[External ID],

"EFGH",MIN(Orders[Document Number]),

"IJKL",[Stay ID of the first visit]),

[EFGH]=[IJKL]))

My issue here is that a customer can buy multiple time during the same month so the measures i used to calculate new and returning customers don't work for transaction count.

Thank you in advance 

Can anyone help me with this ? the formula give less than expected 

[Ashish_Mathur]

Hi,

I cannot understand your requirement.  On the sample data, show the exact result that you are expecting.

[Anonymous]

Hi, 

So i would like to have a table to show the number of transaction each month for returning and new customers.

The outcome would look like this : 

New customers Transactions

Year	Jan	feb	March	Apr	May
2020	1250	1000	5200	526	123

New customers can have two or three transactions so the formulas for new and returning customers count doesn't work since it count unique customers each month 

This is my formulas now for the tables

New Customers transactions = COUNT ( Orders[Transactions ID] ) - [Transactions Returning]

Returning Customers =

COUNTROWS (

CALCULATETABLE (

VALUES ( Orders[External ID] ),

VALUES ( Orders[External ID] ),

FILTER (

ALL ( 'Calendar' ),

'Calendar'[Date] >= MIN( ( 'Calendar'[Date] )

)

)

))

what i encountered is that it doesn't count all the transactions ..

Thank you in advance

[Ashish_Mathur]

Hi,

I just do not understand your question.  Someone who does will help you.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Suppose your tables have relationships like below:

To count the number of transactions per customer, create a measure:

Count of transactions =
VAR customer = SELECTEDVALUE(Customers[Customer ID])
RETURN
CALCULATE(COUNT(Orders[transaction ID ]),FILTER(Orders,Orders[Customer ID] = customer))

Test the result:

My sample data:

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.