Comparison of Customer ID using DAX

[akashpadmakumar]

Hi everyone,

I need help in the following situation,

Data available - Customer ID (not unique values) , Reporting period - (either "Prior Period" or "Current Period")

(One customer ID can have both Prior Period and Current Period)

I need a 3rd column which is Type of Customer - 

1) If customer ID is available in Prior Period and Current Period then it is "Recurring Customer"

2) If customer ID is available in Prior Period and not in Current Period then it is "Removed Customer"

3) If customer ID is available in not Prior Period and in Current Period then it is "New Customer"

I need to calculate this column using Dax, Kindly help.

[mh2587]

https://www.daxpatterns.com/new-and-returning-customers/

[AilleryO]

Hi,

I can do that but can you tell me what you need ? What is not working ?

Having a view of your model might help as well.

[akashpadmakumar]

Customer Type =

var currentCustomer = 'ROT Revenue Subledger'[Customer ID]

var prevPeriod = FILTER( 'ROT Revenue Subledger', 'ROT Revenue Subledger'[Customer ID] = currentCustomer &&'ROT Revenue Subledger'[ROT - Reporting Period (Sorted)] = "Prior Period")

var currentPeriod = FILTER( 'ROT Revenue Subledger', 'ROT Revenue Subledger'[Customer ID] = currentCustomer && 'ROT Revenue Subledger'[ROT - Reporting Period (Sorted)] = "Current Period")

return SWITCH( TRUE(),

NOT(ISEMPTY(prevPeriod)) && NOT(ISEMPTY(currentPeriod)), "Recurring customer",

NOT(ISEMPTY(prevPeriod)) && ISEMPTY(currentPeriod), "Removed customer",

ISEMPTY(prevPeriod) && NOT(ISEMPTY(currentPeriod)), "New customer"

)

Could you check the following query ?

[akashpadmakumar]

I tried this, but the values I am getting is New customer and blanks, could you kindly help me in understanding what went wrong here ?

[AilleryO]

Can you check your variable prevPeriod, because I think you're not getting anything from it.

[AilleryO]

Hi,

In case you haven't read that beautiful article from SQLBI, you should. It will answer your exact question 🙂

https://www.sqlbi.com/articles/computing-new-customers-in-dax/

Have a nice day and do not hesitate to share your solution.

[akashpadmakumar]

Thankyou for the reply,

It seems like similar to my query, but could you help me in writing the DAX for my case ?