Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

customer classification based on revenue last year

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!

 

2 ACCEPTED SOLUTIONS

HI @Anonymous ,

 

Please check @amitchandak solution. I think it should work

 

1.PNG

 

 

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)

View solution in original post

@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))

 

Screenshot 2021-02-10 16.19.34.png

View solution in original post

6 REPLIES 6
harshnathani
Community Champion
Community Champion

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
Not applicable

Hi @harshnathani ,

Thanks for your reply.

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

DouweNobel_0-1612949542188.png

 

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. 

 

HI @Anonymous ,

 

Please check @amitchandak solution. I think it should work

 

1.PNG

 

 

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
Super User
Super User

@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
Not applicable

Hi @amitchandak,

thanks for your fast reply.

 

The formula your posted isn't working. 

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

DouweNobel_0-1612874796313.png

 

Is there a way to fix this? 

 

Thanks a lot already!

 

@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))

 

Screenshot 2021-02-10 16.19.34.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors