March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Dear All,
I need to pull specifically the date of the third order of each customer due to a promotion.
I'm using the solution provided by another user (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Returning-the-THIRD-date/m-p/2741551#M84284). The problem is that the formula ignores sales with same dates.
last date of Top three ascending: =
IF (
HASONEVALUE ( Customer[Customer] ),
MAXX ( TOPN ( 3, Data, Data[Order_Date], ASC ), Data[Order_Date] )
)
As you can see below:
Thank you!
Solved! Go to Solution.
Hi,
I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.
Last date of top three asc: =
VAR _relatedtable =
RELATEDTABLE ( Data )
RETURN
IF (
HASONEVALUE ( Customer[Customer] ),
MAXX (
FILTER (
ADDCOLUMNS (
_relatedtable,
"@rank", RANKX ( _relatedtable, Data[Order_Date],, ASC )
),
[@rank] <= 3
),
Data[Order_Date]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.
Last date of top three asc: =
VAR _relatedtable =
RELATEDTABLE ( Data )
RETURN
IF (
HASONEVALUE ( Customer[Customer] ),
MAXX (
FILTER (
ADDCOLUMNS (
_relatedtable,
"@rank", RANKX ( _relatedtable, Data[Order_Date],, ASC )
),
[@rank] <= 3
),
Data[Order_Date]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |