Forum Discussion
First transaction for each user for each day
Hello everyone,
I was searching all around for DAX for my situation. Hope someone here can help me
So basically i have a table with thousands transactions per day, I need a DAX that is gonna give me earliest transaction for each day for each customer ID, for example if customer had 5 transactions that day, i want to see the first one, and same like that for every unique ID. Slicer for date will be set for only one day. for example only for 03.05.2023. And I want earliest transaction for every user that day.
All best, and thank you in advanced!
Supposing you have a table like:
user_id amount created_at A 1 5/4/2023 10:01 A 2 5/4/2023 10:02 A 3 5/4/2023 10:03 B 11 5/4/2023 10:01 B 22 5/4/2023 10:02 B 33 5/4/2023 10:03 try like:
1)add a calculated column like:date = DATEVALUE([created_at])2)plot a table visual with user_id and date columns with a measure like:
DailyFirstAmt = MINX( TOPN( 1, data, data[created_at], 1 ), data[amount] )it worked like:
4 Replies
FreemanZSuper User
it would be much easier if you could also provide some sample data.
Hi FreemanZ ,thank you for your willingness to help.
So, I'm gonna try to explain as simple as possible.
So basically our customer have multiple transactions per day. What I need is the get their first transaction of certain day. I'm gonna post pic with example.Okay, so i circled couple examples. so you see user 3659 had two transaction, first one is 5.00$ at 19:55, and the other one is for 10.00$ at 19:45. Some users only have one transaction per day which is alright. But for those with multiple transactions i need DAX to return first transaction for that day for each user(in this case it is user-3658,amount 10.00$,time 19:45). So I need earliest transaction for each day, becouse most of the users have transactions every day. I want to see what is their first transaction in a day for every user.
I hope I explained well.
Thank you!- FreemanZ
Supposing you have a table like:
user_id amount created_at A 1 5/4/2023 10:01 A 2 5/4/2023 10:02 A 3 5/4/2023 10:03 B 11 5/4/2023 10:01 B 22 5/4/2023 10:02 B 33 5/4/2023 10:03 try like:
1)add a calculated column like:date = DATEVALUE([created_at])2)plot a table visual with user_id and date columns with a measure like:
DailyFirstAmt = MINX( TOPN( 1, data, data[created_at], 1 ), data[amount] )it worked like:
Perfect FreemanZ,
This is exactly what i was looking for! You really helped!
Thank you, all the best!
