topic How to get the penultimate order date per customer from order data in DAX Commands and Tips

How to get the penultimate order date per customer from order data

tykio — Sun, 07 Aug 2022 05:54:23 GMT

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

penultimate order date=

CALCULATE(

MIN('order'[Order Date],

TOPN(

'order',

'order'[Order Date]

)

))

thanks for all the tips and help @!

Re: How to get the penultimate order date per customer from order data

Jihwan_Kim — Sun, 07 Aug 2022 06:43:49 GMT

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 ) ) ) )

Re: How to get the penultimate order date per customer from order data

tykio — Sun, 07 Aug 2022 15:01:21 GMT

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!

Re: How to get the penultimate order date per customer from order data

Jihwan_Kim — Mon, 08 Aug 2022 03:00:42 GMT

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?

Re: How to get the penultimate order date per customer from order data

tykio — Mon, 08 Aug 2022 06:24:06 GMT

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

Re: How to get the penultimate order date per customer from order data

Jihwan_Kim — Mon, 08 Aug 2022 06:45:35 GMT

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?

Re: How to get the penultimate order date per customer from order data

tykio — Mon, 08 Aug 2022 07:29:01 GMT

Jihwan_Kim thanks for reply

Yes,i want to create a calcluated table 🙂 thanks for your help

Re: How to get the penultimate order date per customer from order data

Jihwan_Kim — Mon, 08 Aug 2022 08:27:37 GMT

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