Help with counts of transactions done in a day broken out by a field

[trevordunham]

I have data that is broken out by transactions by type via date as well as a user ID. I was hoping to find a way to get the count of transactions done in the same day per user ID. Further, I would like the number of User IDs  that had more than 1 transaction in the same day.

[trevordunham]

User ID	Date	Tranaction
AAA	1/1/2022	A
AAA	1/1/2022	B
AAA	2/2/2022	C
BBB	1/1/2022	D
BBB	1/5/2022	D
CCC	1/1/2022	A
CCC	1/1/2022	B
CCC	1/2/2022	C
CCC	1/2/2022	C

I am not sure how to paste sample data but this is generally what the data is like. I want to count how mant transactions are in the same day by each user. Also I want to count how many users had 2+ transactions in the same day. 

[KNP]

Have a look at the attached PBIX file and see if it helps.

[trevordunham]

The calculation does not seem to work. It shows 3 users with more than 1 transaction in a day but only AAA and CCC did more than transaciton in a day. I am looking for specifically the count of users with subsequent transactions in the same day.

[KNP]

Apologies, try the attached (updated) PBIX.

Just check to make sure it works in all of your scenarios.

[trevordunham]

This works great, one last request: how would I get a count of the subsequetn transactions that were in the same day. So for this test data, A would have 1, C would have 2, so a count of 3 and say if A had 3 tranasctions done on the same day the count would hit 2 for a total of 4. Is this the previous calulation you did?

Thanks!

[KNP]

I'm not sure if I fully understand but I've added the original calc back in and called it '

Count of Multiple Transactions per Day'. See attached.

It may not be quite what you're after but let me know.

[trevordunham]

Sorry for the late response but the original calc does not seem to do it. I am looking for the count of subsequent transactions done in a day. So if User AAA would have had 3 transactions in one day, then 2 in another day, they would have done 3 total subsequent transactions. 

[KNP]

Sorry, I don't understand.

"So if User AAA would have had 3 transactions in one day, then 2 in another day, they would have done 3 total subsequent transactions."

How does the 3 in one day and the 2 in another equal the 3 in total?

[trevordunham]

In this example user AAA had 2 subsequent transactions in one day, then they had one subsequent transaction on another day. This gets us our 3 total subsequent transactions.

In my use case, subsequent transaction = sum of all transactions done on the same day after the first transaction

To give some context, a process was changed to allow more than one transaction to happen in a day per user. I want to find out the sum of these subsequent transactions that are now being done that could not be done before. The first transaction could always be done, the subsequent transactions in the same day could not.

[KNP]

Sorry, I'm still not following.

In the summary data provided, user AAA only had one transaction on the second day, how is that considered 'subsequent' as it is the first for that day?

User ID	Date	Tranaction
AAA	1/1/2022	A
AAA	1/1/2022	B
AAA	2/2/2022	C
BBB	1/1/2022	D
BBB	1/5/2022	D
CCC	1/1/2022	A
CCC	1/1/2022	B
CCC	1/2/2022	C
CCC	1/2/2022	C

And for this example data, what would the subsequent transactions for CCC be?

2 right?

[trevordunham]

In the example data AAA only has one subsequent transaction and CCC has 2, that is correct, for a total of 3. Sorry if it was not clear, I just wanted to explain another situation with more subsequent transactions so that you were clear what it meant. With the current data set the first calculation you did was correct in the result of 3 but the calculation was off when I tried to add more subsequent transactions. That's why I did another hyptohetical sitaution 

[KNP]

Ok, makes sense.

Can you please paste some sample data that covers these other scenarios and I'll add it in to the solution.

[trevordunham]

User	Date	Transaciton
AAA	1/1/2022	A
AAA	1/1/2022	B
AAA	1/1/2022	C
BBB	1/1/2022	A
BBB	1/5/2022	A
CCC	1/1/2022	A
CCC	1/1/2022	B
CCC	1/5/2022	A
CCC	1/5/2022	C
DDD	1/2/2022	A
DDD	1/2/2022	B
DDD	1/2/2022	C
DDD	1/2/2022	D

Here is some data that should work, AAA should show 2 subsequent transactions, CCC should show 2 subsequent transactions and DDD should show 3 subsequent transactions for a total of 7.

[KNP · Accepted solution]

Thank you, I think I've got it now.

Check the attached PBIX file.

[trevordunham]

Thank you for all your help!

[KNP]

Hey @trevordunham, 

I see that this was marked as solved by a CST member, not you. 

I'm just checking, is the issue actually solved for you?

[trevordunham]

Yes, they just beat me to it. Appreciate it

[KNP]

Awesome, just making sure. Sometimes they get a little eager before the issue is solved. 

Glad I could help.

[KNP]

Thanks for the data, that helps.

What do you want tthe output to look like? i.e. is it going in a card, table, something else?

[KNP]

Paste some sample data (not a screenshot) that includes all these scenarios and I'm sure you'll get an answer quickly.

A screenshot of your data model would also be useful.