Help with Time Intelligence formula

Hi guys,

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?

  1. 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?

  2. 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?

image

Cheers,

Kashif

Hi @K.Zarar,

Please try to create Measure using below formula: Also do you have dates table as time intelligence measure works only if you have dates table and it has continuous dates. Ideally, first argument of DATEADD in this case should be Dates column from dates table.

Trans_LM := CALCULATE( [Count_trans],
                                                                      DATEADD(‘Table1’[Created On], -1, MONTH))

See result of following formula in snapshot:

image

Additionally, you can see below Sam’s video to create data table. Code of date table is also given in the comment’s section.

Once you have created data table, you need to mark it as date table. Then you can create relationship between ‘Table1’[Created On] and date column of date table. Once all is setup, any time intelligence will work perfectly and below formula will work fine as well.

Trans_LM := CALCULATE( [Count_trans],
DATEADD(‘Date’[Date] -1, MONTH))

Hi @K.Zarar, we’ve noticed that no response has been received from you since 20st of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!