customer classification based on revenue last year

[Anonymous]

Hi all,

I'm quite new to DAX and PowerBI.

My plan is create an extra collum in my table to classified customers based on their revenue in 2020. 

The classification:

Customers with more than 500.000 EURO in 2020 are classificed as 1

Customers between 100.000 and 499.999 in 2020 are classified as 2

Customers between 50.000 and 99.999 in 2020 are classified as 3

Customers between 10.000 and 49.999 in 2020 are classified as 4

Customers between 1 and 9.999 in 2020 are classified as 5

Customers with a reveune lower then 1 euro in 2020 are classified as 6 

So what i did:

Create a measure to calculate the last year revenue:

Sales Last year in EURO = CALCULATE(SUM(Sales [revenue]),SAMEPERIODLASTYEAR('Calendar'[Date]))

After that I am trying to create the classification part in an new measure.

Customer classification =

IF( [Sales Last year in EURO] > 500000,

1,

IF(AND([Sales Last year in EURO] >= 100000, [Sales Last year in EURO] < 499999),

2,

IF(AND([Sales Last year in EURO] >= 50000, [Sales Last year in EURO] < 99999),

3,

IF(AND(Sales Last year in EURO] >= 10000, [Sales Last year in EURO] < 49999),

4,

IF(AND([Sales Last year in EURO] >= 1, [Sales Last year in EURO]< 9999),

5,6)))))

It works but it gives the wrong classification: it takes the sales of all previous years. (2019 + 2020)

So customers with a sales of 400.000 in 2020 needs to get a 2 but he caculate the sales of 2020 (400.000) and 2019 ( 350.000) so it is more than the 500.000 so it is classified as 1.

So something is wrong in the formula. 

Thanks a lot for your advice!

[harshnathani · Accepted solution]

HI @Anonymous ,

Please check @amitchandak solution. I think it should work

Sales LY1 = 
var _max = YEAR(MAX(Dates[Date]))
Return
//_max
CALCULATE( [Total Sales] , FILTER(Dates, Dates[Year] = _max - 1))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

HI @Anonymous ,

Check the file and let me know if this is what i needed.

I have used dummy data and classification but seems to work for sales LY.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani ,

Thanks for your reply.

What I see in your pbix document is still the wrong formula.

Aron Tucker has a following sales:

2018 = 17.789

2019 = 17.747

2020 = 2.622

The formula for sales LY1 = should be calculating only the sales of last year. 

So in this example last year should be 2020. 

The colom needs to show 2.622 as sales last year.

But he calculated the sales of the last 2 years. (17.747 + 2.622)

So he takes 2019 and 2020 but i needs to take only 2020.

I hope my questions is clear. 

Goals is to see in the measure "Sales LY1" only the sales of last year so only 2020 so 2.622 for Aaron Tucker.

I hope this explains my question. 

[harshnathani · Accepted solution]

HI @Anonymous ,

Please check @amitchandak solution. I think it should work

Sales LY1 = 
var _max = YEAR(MAX(Dates[Date]))
Return
//_max
CALCULATE( [Total Sales] , FILTER(Dates, Dates[Year] = _max - 1))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , Try like this, This will fix year

Sales Last year in EURO =
var _max = year(maxx(allselected('Calendar'), 'Calendar'[Date]))
return
CALCULATE(SUM(Sales [revenue]),filter(all('Calendar') , 'Calendar'[Date] = _max -1))

[Anonymous]

Hi @amitchandak,

thanks for your fast reply.

The formula your posted isn't working. 

it doesn't shows any value's (see screenshot)

Is there a way to fix this? 

Thanks a lot already!

[amitchandak · Accepted solution]

@Anonymous , I tried this on my system. I reduced 2 year because of data

Measure 6 = var _max = maxx(ALLSELECTED('Date'), 'Date'[Year]) -2 return CALCULATE([Sales],FILTER(all('Date'), 'Date'[Year] =_max))