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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.