Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I want to ask how to take out the penultimate order date of each customer,I have tried using the following formula,but doesn't work
thanks for all the tips and help @!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
New Table =
VAR _lastorderdate =
ADDCOLUMNS (
VALUES ( Customer[Customer ID] ),
"@LastOrderDate",
MAXX (
FILTER ( 'Order', 'Order'[Customer ID] = EARLIER ( Customer[Customer ID] ) ),
'Order'[Order Date]
)
)
VAR _penultimatedate =
ADDCOLUMNS (
_lastorderdate,
"PenultimateOrderDate",
MAXX (
FILTER (
'Order',
'Order'[Customer ID] = EARLIER ( Customer[Customer ID] )
&& 'Order'[Order Date] < [@LastOrderDate]
),
'Order'[Order Date]
)
)
RETURN
_penultimatedate
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.
@Jihwan_Kim thanks for reply ,please check the URL
https://drive.google.com/file/d/1sgll6K_YHXMRdU4VEPk3QQ8XckJCvu-l/view?usp=sharing
I want to add the last consumption date and the penultimate consumption date to the output data, and then calculate the member's consumption cycle.
Thanks for your kindly help
Hi,
Sorry but your pbix file looks like below and I cannot proceed.
Did you mean that you want to create a calcluated table instead of the visualization?
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.
@Jihwan_Kim thanks for reply
Yes,i want to create a calcluated table 🙂 thanks for your help
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
New Table =
VAR _lastorderdate =
ADDCOLUMNS (
VALUES ( Customer[Customer ID] ),
"@LastOrderDate",
MAXX (
FILTER ( 'Order', 'Order'[Customer ID] = EARLIER ( Customer[Customer ID] ) ),
'Order'[Order Date]
)
)
VAR _penultimatedate =
ADDCOLUMNS (
_lastorderdate,
"PenultimateOrderDate",
MAXX (
FILTER (
'Order',
'Order'[Customer ID] = EARLIER ( Customer[Customer ID] )
&& 'Order'[Order Date] < [@LastOrderDate]
),
'Order'[Order Date]
)
)
RETURN
_penultimatedate
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,
Please check the below picture and the attached pbix file.
I think there are many ways to solve the problem, but I tried to use TOPN function like what it is in your measure.
Please check if it suits your requirement.
penultimate order date =
IF (
COUNTROWS ( 'Order' ) >= 2
&& HASONEVALUE ( Customer[Customer ID] ),
CALCULATE (
MIN ( 'Order'[Order Date] ),
KEEPFILTERS (
TOPN (
2,
ALL ( 'Order'[Order Date] ),
CALCULATE ( MAX ( 'Order'[Order Date] ) ), DESC
)
)
)
)
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 @Jihwan_Kim
Thanks for your reply 🙂 very useful
there is another question,
Is there an easier way if I want to output the results as a data rather than a report?
so many thanks!
Hi,
Thank you for your message.
Sorry that it is difficult to understand your last question.
Could you please share your sample pbix file's link with how the expected result looks like?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |