Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
madzgalj_darko
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

hi @madzgalj_darko 

Supposing you have a table like:

user_idamountcreated_at
A15/4/2023 10:01
A25/4/2023 10:02
A35/4/2023 10:03
B115/4/2023 10:01
B225/4/2023 10:02
B335/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:

FreemanZ_1-1683166835431.png

 

 

View solution in original post

4 REPLIES 4
madzgalj_darko
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.

Example.png

 

 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! 

hi @madzgalj_darko 

Supposing you have a table like:

user_idamountcreated_at
A15/4/2023 10:01
A25/4/2023 10:02
A35/4/2023 10:03
B115/4/2023 10:01
B225/4/2023 10:02
B335/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:

FreemanZ_1-1683166835431.png

 

 

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

FreemanZ
Super User
Super User

hi @madzgalj_darko 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors