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

[tykio]

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(

        2,

        'order',

        'order'[Order Date]

    )

))

thanks for all the tips and help @!

[tykio]

@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 

[Jihwan_Kim]

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?

[tykio]

@Jihwan_Kim thanks for reply

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

[Jihwan_Kim · Accepted 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

[Jihwan_Kim]

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

[tykio]

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!

[Jihwan_Kim]

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?