I have a table, let’s call it Table 1 where I’ve got columns A50 and Created On fields.
What do these fields mean?
- The table, Table 1 contains transactions from customers
- A50 is the unique customer id
- Created On is the date the customer made a transaction (the table contains more than 1 transaction per customer)
What I want out of this table?
I want to create a measure that counts the number of transactions each customer made, I did by creating a measure Count_trans := COUNT(‘Table1’[A50]) . Is this the best way?
What I also want to do is, see how many of these transactions were made last month by totally new customers.
I think there are two steps to this problem,
i) I created a measure called Trans_LM := CALCULATE( [Count_trans],
DATESINPERIOD(‘Table1’[Created On], LASTDATE(‘Table1’[Created On]), -1, MONTH))
ii) After this measure was created, I was going to check whether, Count_Trans == Trans_LM for these customers, and if they are equal, it means they are new customers.
My problem is, upon creating the measure in Step 2i, I’m getting the wrong info when I drag it onto the table. It’s showing customers have made a transaction in the last month for customers that haven’t made any transaction.
Are you able to help out?