Categorize customers based on buying

[AlexanderMeneik]

Hello everyone, this is Alexander from Hamburg Germany, this is my first post here.

I have been doing research for this problem for about 2 weeks now and I am running out of ideas. 

The client is a machine builder who sells big machines all over the world.

The tables in question are the FACT "Invoices" table which includes CustomerID of course, Date Initiated (when project was started) and Date Final Invoice (when invoice was issued), usually a few weeks / months apart, revenue amount and direct cost. Then there is the DIM Customers table.

Date range from 2019 when the company was started until 2023 so far.

The usual reports about revenue, world map with revenue bubbles, margin, product categories, all works completely fine. It is only this last step where I am stuck.

For every quarterly report, they want a list of customers, where the most right column says one out of 3 categories:

NEW CUSTOMER - bought for the first time in the year selected, but never before

STEADY CUSTOMER - has bought at least once in the last 2 years

OLD CUSTOMER - has bought once but not in the last 2 years

All based on the "Date_Initiated".

I have tried to get at this with

VAR YearSelected = SELECTEDVALUE(Date[Year]), but I get errors that I do not understand.

I have tried to set variables with logic and RETURN values with a SWITCH function, but that got me other errors.

With some Nested IFs, I got a partial solution, but I am struggling with the correct time logic.

If you have any idea, please explain like you would to a Golden Retriever - I am novice to this and have only done very simple aggregation, SUM and DIVIDE functions so far.

Thanks a lot.

[v-xuxinyi-msft]

Hi @AlexanderMeneik 

I ran the testing with sample data I created myself, maybe it can help you.

My sample:

Then I created a measure as follows.

Measure = 
VAR _countlastyear = CALCULATE(COUNT('Fact'[ID]),FILTER(ALLEXCEPT('Fact', 'Fact'[ID]), YEAR('Fact'[StartDate]) < YEAR(SELECTEDVALUE('Table'[Date]))))
VAR _countthisyear = CALCULATE(COUNT('Fact'[ID]), FILTER(ALLEXCEPT('Fact', 'Fact'[ID]), YEAR('Fact'[StartDate]) = YEAR(SELECTEDVALUE('Table'[Date]))))
VAR _countlasttwoyear = CALCULATE(COUNT('Fact'[ID]), FILTER(ALLEXCEPT('Fact', 'Fact'[ID]), YEAR('Fact'[StartDate]) >= YEAR(SELECTEDVALUE('Table'[Date])) - 2 && YEAR('Fact'[StartDate]) < YEAR(SELECTEDVALUE('Table'[Date]))))
RETURN
SWITCH(TRUE(),
_countthisyear <> BLANK() && _countlastyear = BLANK(), "NEW CUSTOMER",
_countlasttwoyear <> BLANK(), "STEADY CUSTOMER",
_countlastyear <> BLANK() && _countlasttwoyear = BLANK(), "OLD CUSTOMER"
)

Is this the result you expect?

For a detailed description of the formulas I used, you can read related document link:

CALCULATE function (DAX) - DAX | Microsoft Learn

FILTER function (DAX) - DAX | Microsoft Learn

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

COUNT function (DAX) - DAX | Microsoft Learn

SELECTEDVALUE function - DAX | Microsoft Learn

YEAR function (DAX) - DAX | Microsoft Learn

BLANK function (DAX) - DAX | Microsoft Learn

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. We can better understand the problem and help you.

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexanderMeneik]

Wow, thank you very much. I realize that my understanding of DAX at this point is so low that I can not really understand that code. It will take me a few days to go through it.

[ValtteriN]

Hi,

Here is one way to do this:

Demo data (table 26 is just A,B and C):

Relationship:

Dax:

Customer category =

var _curYear= SELECTEDVALUE('Calendar'[Year]) //we are using selectedvalue to refer to current time period

var _pastYear1 = _curYear-1

var _pastYear2 = _curYear-2 //these two are for steady customer calculation

var _current =

COUNTROWS('Table (25)') //how many purchases there are in selected period

var _past2 =

CALCULATE(COUNT('Table (25)'[fact]),FILTER(ALL('Calendar'), 'Calendar'[Year]  in {_curYear,_pastYear1,_pastYear2})) //we remove time filters with ALL and apply the limitation that year should be in past 3 years

var _alltime = CALCULATE(COUNT('Table (25)'[fact]),FILTER(ALL('Calendar'), 'Calendar'[Year]<=_curYear)) //all sales before or equal current selection

var _category = SWITCH(TRUE(),

_past2>_current,"Steady customer",

_alltime>_current,"Old customer",

_current=0,BLANK(), //this is needed when looking at past

"New customer") //switch + true = multiple IF conditions. We test for different scenarios

RETURN

_category //we return the category variable

End result:

In the bottom table visual I have customer from table (26) and the measure shown above.

The value changes dynamically based on selection. If you want to have historical data you should add this kind of logic to your database as a column. 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[AlexanderMeneik]

Thanks a lot. It will take me a while to get my head around this.