Need help with Time Intelligence function

[Anonymous]

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?

Cheers,

Kashif

[Anonymous]

Hi @K_Zarar ，

Whether your problem has been solved? If yes, could you please mark the helpful post as Answered? It will help others easily to find the solution when they get the same issue.

Best Regards

Rena

[Anonymous]

Hi @Anonymous ，

You can create one measure as below:

NeworOldCustomer =

var a=max('Table1'[A50 ]) 

var b=CALCULATE(COUNT('Table1'[A50 ]),filter('Table1',max('Table1'[A50 ])=a))

var c=LASTDATE('Table1'[Created On])

VAR d = CALCULATE(COUNT('Table1'[A50 ]),FILTER('Table1', 'Table1'[Created On]

                >= DATE ( YEAR ( c ), MONTH (c ) , 1)

                && 'Table1'[Created On]

                    <=  DATE ( YEAR ( c ), MONTH (c ) +1, 1) &&MAX('Table1'[A50 ])=a)) var f=DATE ( YEAR ( c ), MONTH (c ) - 1, 1)

VAR e = if( d=b,"New Customer","Old Customer")

return  e

Best Regards

Rena

[amitchandak]

To make time intelligence function work, make sure you have a date table.

See examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin