CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
EmilL
EmilL

Reputation: 62

Giftcard customer transitioning into "real" customer calculation in DAX

I am trying to calculate whether a customer that uses a gift card as his first order, will transition into become a "normal" customer, which means that he/she orders without a gift card next time.

I can see from the PaymentMethod how the customer payed (PaymentMethod = "Gift card")

I have made a column that says "Gift card" if the order is purchased with this, otherwise it is blank.

So basically I want to make a column that states: If the customer has their first purchase with a gift card, and makes a new order later, that is not purchased with a gift card. So for example a 1 if TRUE, a 0 if FALSE.

I have the following columns in my orders table:

  1. OrderID
  2. CustomerID
  3. BookingDate
  4. PaymentMethod (Cash, Credit Card, Gift card etc.)
  5. Gift card (Blank or Gift card)

I have tried different DAX codes, but the thing is - I don't know how I would write that the first order has to be a gift card order, and the next order from the customer has to be a non-gift card order, so I doubt the DAX code that I have tried, would be relevant.

Here is a picture of what I hope to achieve: Column I try to achieve

I hope someone can lead me in the right direction, thank you in advance.

Upvotes: 0

Views: 37

Answers (2)

EmilL
EmilL

Reputation: 62

With the help of Alexis i ended up with this, that returns a 1 if a customer uses a giftcard for their first order, and something else than giftcard for their second order.

FirstOrderGiftNextOrderNotGift = 
VAR FirstOrderID =
    CALCULATE (
      MIN ( orders[Column1.Order_ID] ),
        ALLEXCEPT ( orders, orders[Column1.Customer_ID] )
    )
VAR FirstOrderGiftCard =
    CALCULATE (
        VALUES ( orders[Column1.PaymentMethod] ),
        ALLEXCEPT ( orders, orders[Column1.Customer_ID] ),
        orders[Column1.Order_ID] = FirstOrderID,
        orders[Column1.PaymentMethod] = "Giftcard"
    )
VAR PaymentMethods =
    CALCULATE (
        DISTINCTCOUNT ( orders[Column1.PaymentMethod] ),
        ALLEXCEPT ( orders, orders[Column1.Customer_ID] )
    )
RETURN
    IF (
        FirstOrderGiftCard = "Giftcard"
            && orders[ReturningCustomer] = "Yes"
            && PaymentMethods > 1,
            1,
        0
    )

After calculating this column I just created a measure for counting the distinct 1's of the customer numbers.

CountOfCustomersWhoTransitioned =
CALCULATE (
    DISTINCTCOUNT ( orders[Column1.Customer_ID] ),
    orders[FirstOrderGiftNextOrderNotGift] = 1
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

I'd suggest something along these lines:

TransitioningCustomer =
VAR FirstOrderDate =
    CALCULATE ( MIN ( Data[BookingDate] ), ALLEXCEPT ( Data, Data[CustomerID] ) )
VAR FirstOrderGiftCard =
    "GC"
        IN CALCULATE (
            VALUES ( Data[PaymentMethod] ),
            ALLEXCEPT ( Data, Data[CustomerID] ),
            Data[BookingDate] = FirstOrderDate
        )
VAR PaymentMethods =
    CALCULATE (
        DISTINCTCOUNT ( Data[PaymentMethod] ),
        ALLEXCEPT ( Data, Data[CustomerID] )
    )
RETURN
    IF ( FirstOrderGiftCard && PaymentMethods > 1, 1, BLANK () )

This calculates the first order date and then check whether a gift card was used on that date. It also counts the total number of (distinct) payment methods used.

The conclusion that if a gift card was used on the first date and there was some other type of payment method used as well as some point in the customer's purchase history, then that is a transitioning customer.

Upvotes: 1

Related Questions