The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]
)
)
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]
)
)
User | Count |
---|---|
26 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |