DAX to determine different transaction types by same customer on same day

Question

Hi,

We effectively have two types of transactions, 'customer orders' and 'cash and carry'. To state the fairly obvious, 'Customer Orders' are those placed by the customer in advance and collected later and 'cash and carry' is where the customer walks into the store and purchases an in stock item on the spot. We recognise the 'Customer Order' sale when the customer collects the item and pays the remaining balance.

My aim is to analyse where in the process of Finalising a Customer Order, the customer also makes additional purchases from the store that would be processed as a totally separate transaction.

I either need a calculated column that flags the 'Cash&Carry' records where that customer has also conducted a 'Customer Order' transaction on the same day or a measure that will sum the sales under the same conditions.

Any help appreciated.

Jacob

Answer 1

Hi Jacob,

The following formula will create the calculated column:

=IF([Transaction Type]="Cash&Carry" &&
     CALCULATE(
          COUNTROWS(Transactions);
          ALLEXCEPT(Transactions;Transactions[Customer Reference];Transactions[Date]);
          Transactions[Transaction Type]="Customer Order") > 0;
     "Yes";
      "No"
      )

Answer 2

Frederik, this works like a charm! Thank You.

Can you explain how this bit works:  ALLEXCEPT(Transactions;Transactions[Customer Reference];Transactions[Date])

I understand the rest its just its not intuitive to me - I guess it has to create a table that includes everything except where Customer Reference and Date are the same as the current row!

Answer 3

Exactly Jacob,

CALCULATE() turns the row context into a  filter context.  With the 2nd, 3rd, ... argument we can create a new filter context.

ALLEXCEPT(Transactions;Transactions[Customer Reference];Transactions[Date])

means ignore all filters but keep those on Customer Reference and Date, or other words look for rows on the same date with the same customer reference

The 3rd argument of CALCULATE (Transactions[Transaction Type]="Customer Order") means: only look for Customer Orders.

If we find more than 1 row return Yes, otherwise return No.

Answer 4

Powerful stuff! Thanks again.