Count how many times a transaction is made, for each costumer

[carol_mar]

Hello,

hope you'll be able to help me..

So, what I have is:

2 Tables related to each other by a many to one relationship. 

The first table contains the names and the Serial Number of each costumer

The secondo table is made by many coloums (Serial Number - day and time of the transaction - transaction $ -...-...)

I need to know with which frequency each costumer make a transaction, like:

3 times per day this costumer (Serial Number) has made a transaction and how much is the total amount

and 

10 costumer make 4 transaction every day

How can I do that?

Thank you so much

[az38]

Hi @carol_mar 

I think you dont even need a programming with your data model

Just add to visual (table for example) columns:

Hierarchy date, Serial Number, Transaction ID (or smth like that) and AMount

After go to field pane and set Transaction ID aggregation as Count and Amount Aggregation as Sum

Then pick your DateTime column, go to the Modeling ribbon and set Data Type as Date

In the most common case it should work perfect

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[carol_mar]

Thank you su much for your time. 

I think it probably works but now, (I'm not an expert yet ahah) I would need to verify it, creating a chart. I obtained a table with "year" "quarter" "monthly" "day " "count of Id" "amount" which make absolutily sense. 

What I would need now is: in the X axis the days and in the Y the number of transaction uccuring before that day cause I need to say for example:

10 costumer make 1 transaction per day

15 costumer make 2 transaction per day

2 costumer make 3 transaction per day..

Is it possible? Thank you soo much for helping!

[az38]

@carol_mar 

try to create a new calculated table like

Table2 = SUMMARIZE(
'Table',
'Table'[DateTime], 'Table'[Serial Number],
"Count Transactions", COUNT('Table'[Transaction ID]),
"Total Amount", SUM('Table'[Amount])
)

then you could easy (even if you are still not expert 🙂 ) create a bar-chart

date as Axis

and count of transaction id as Value

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution

[carol_mar]

Thank you so much @az38 !

I have another question now.. ahah

Do you have idea of how I can display, for EACH costumer, how many days are in between 2 transaction that he made? Again I have a Table with these coloums: 

serial number - transaction amount - date and time of each transaction (everything for 1 month) - ... - ... 

I would like to obtain the following table:

Serial Number - date - number of paymnt before that date - date - days in between 

Basically i need to know the frequency of these payment.. if the make a transaction every day or each two days etc..

Save meee 😬

Thank you so much!!

[az38]

@carol_mar 

just give an example of your data source and outpu with some dummy data

do not hesitate to give a kudo to useful posts and mark solutions as solution

[carol_mar]

As you can see Serial number is repeated more times in a day (t means number of transaction).

The time I don't care.

I needto know this guy SM60R-05-0000A248 (i.e.) how often make a transaction, like:

1 transaction per day

1 transaction each 2 days

.... etc

THANK YOU😅😊

amount	Serial_number	date	time
100	SM60R-05-0000A248	01/04/2019	03:17:22
56	SM60R-05-0000A248	01/04/2019	04:37:39
50	SM5R-04-00006F20	01/04/2019	05:30:26
100	SM5R-04-00006F7A	01/04/2019	07:13:32
150	SM5R-04-00006F83	01/04/2019	07:37:29
100	SM5R-04-00006EA7	01/04/2019	07:37:40
100	SM5R-04-00006F03	01/04/2019	07:42:33
100	SM5R-04-00006F03	01/04/2019	07:43:45
100	SM60R-05-0000A239	01/04/2019	07:48:11
50	SM5R-04-00006EE9	01/04/2019	09:07:57
300	SM60R-05-0000A258	01/04/2019	09:48:33
100	SM5R-04-00006FC6	01/04/2019	11:35:17
100	SM5R-04-00006EA7	01/04/2019	14:02:50
50	SM5R-04-00006F96	01/04/2019	14:10:47
90	SM5R-04-00006F27	01/04/2019	14:11:12
100	SM60R-05-0000A239	01/04/2019	14:58:01

[az38]

@carol_mar 

if your data will look like 

amount	Serial_number	date	time
100	SM60R-05-0000A248	01.04.2019	3:17:22
56	SM60R-05-0000A248	01.04.2019	4:37:39
5454	SM60R-05-0000A248	07.04.2019	4:37:39
343	SM60R-05-0000A248	01.05.2019	4:37:39
23	SM60R-05-0000A248	02.05.2019	4:37:39
54	SM60R-05-0000A248	01.01.2020	4:37:39
6	SM60R-05-0000A248	02.01.2020	4:37:39

what exactly result do you expect?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[carol_mar]

@az38  something like:

Serial number	Date	payment [1-100]	Previous payment Date	Days in Between
SM60R-05-0000A248	1st April	1	0	-
SM60R-05-0000A248	3rd April	2	1st April	2
		...	3rd April
SM60R-05-0000A454	2st April	1	0	-
SM60R-05-0000A454	6rd April	2	2st April	4
		...	6rd April	-
SM60R-05-0000A666	1st April	1	0	-
SM60R-05-0000A666	10rd April	2	1st April	9
		...	10rd April	-

means that I can have a table were for each costumer, considering a range of max 100 transaction per day(which is super high) i know the real number of days after each transaction. Do you get what I mean?

[az38]

@carol_mar 

what is payment [1-100] column? simple rank? if so, try column

payment [1-100] = RANKX( ALL([Serial number]), [Date])

also try measures

Previous payment Date = 
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Serial number]), Table[Date]<MAX(Table[Date]))
RETURN
if(ISBLANK(_prevDate), 0, _prevDate)

 and 

Days in Between = DATEDIFF([Previous payment Date], SELECTEDVALUE(Table[Date), DAY)

do not hesitate to give a kudo to useful posts and mark solutions as solution