cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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!

1 ACCEPTED SOLUTION
Super User

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:

``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 4
Frequent Visitor

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!

Super User

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:

``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:

Frequent Visitor

Perfect @FreemanZ,
This is exactly what i was looking for! You really helped!
Thank you, all the best!

Super User

it would be much easier if you could also provide some sample data.

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors