Number of days between orders for the same customer

[urosgodnov]

Hi community!

I have a problem with DAX. I would like to get the number of days between orders (not the same product) for a customer. I've watched a youtube video "Calculate The Difference In Days Between Purchases Or Events In Power BI Using DAX" and tried to implement his solution with the addition for the current customer:

daysBetweenOrders =

VAR indexNum =

    MAX (SalesHeader[Index])

VAR ThisCustomer =SELECTEDVALUE(SalesHeader[partnerid])

VAR PreviousIndexNUm =

    CALCULATE (

        MAX ( SalesHeader[Index] ),

        FILTER ( ALLSELECTED ( SalesHeader ), SalesHeader[Index] < indexNum )

    )

VAR CurrentDate =

    VALUE ( SELECTEDVALUE ( SalesHeader[order_date] ) )

VAR PriorDate =

    VALUE (

        CALCULATE (

            SELECTEDVALUE ( SalesHeader[order_date] ),

            FILTER ( ALL ( SalesHeader ), SalesHeader[Index] = PreviousIndexNum), FILTER(ALL(SalesHeader), SalesHeader[partnerid]=ThisCustomer)

        )

    )

RETURN

    IF (

        indexNum = CALCULATE ( MIN ( SalesHeader[Index] ), ALLSELECTED ( SalesHeader ) ),

        0,

        CurrentDate - PriorDate

    )

And here Im getting lost. The problem is, that the same customer can have different partnerid, so my unique value is email, which is stored in a table customer with the keys (partneid).

For example:

Let's pretend that the partnerid 265100025029 and 26510002563 are the same customer with the email john.doe@gmail.com.

How would I alter the previos dax code to add additional column, where I would have calculated days between orders by customers?

Any help is highly appreciated.

Uros

[daXtreme]

What is it that you're trying to calculate? The average number of days between orders? Or the number of days between the latest order in the current context and the previous one? Or maybe the number of days between the latest order in the context and the one before without taking into account the current context? It's not clear what it is and there are many possibilities. As I've already said, by writing a general measure and then slicing by dimensions you can easily get many different meaningful results. If you, for instance, slice by emails, you should get what you want (but you have to really specify exactly what the measure means).

[tamerj1]

Hi @urosgodnov 

Would you please provide a screenshot of the expected visual for better understanding?

[daXtreme]

@urosgodnov 

Actually, it's easier than you think. Create the measure to be fully general, without paying attention to parterid or customer at all. Just calculate the difference between what's currently been selected. Then by selecting a single email address from your dimension you'll get what you want.