cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markPowerBi
Frequent Visitor

Rank and show Past 3 months sales

Hi Everyone

I have 1 table CustomerName, Sales, And MonthYear

The tables has 10 different customers with sales from Jan2019 - Sep2019

I created a rank that show top 2 and top 5 customer based on the selected month in slicer.

Now, I want to create a pivot/matrix tables when I select top 2 and select Aug2019 will show only the top 2 customers with their corresponding sales for the past 3 months from Jun19-Aug19. I need this to make a tend lines graph by customers..

Thank you for you help..
17 REPLIES 17
Shamsbutt
Helper II
Helper II

Hello @markPowerBi ,

 

Please follow this Link to show last N months data based on selection

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

 

and Follow this link to select top N customers by sales

https://www.youtube.com/watch?v=SsZseKOgrWQ

 

Thanks

Reagrds

Adeel

markPowerBi
Frequent Visitor

Here is my sample tables below.

 

Customer Table:

CustomerNameSalesMonthly
Cust 1531/1/2019
Cust 2862/1/2019
Cust 363/1/2019
Cust 4814/1/2019
Cust 5465/1/2019
Cust 1742/1/2019
Cust 2721/1/2019
Cust 3288/1/2019
Cust 4299/1/2019
Cust 12610/1/2019
Cust 2912/1/2019
Cust 3212/1/2019
Cust 4621/1/2019
Cust 5252/1/2019
Cust 1453/1/2019
Cust 1884/1/2019
Cust 2984/1/2019
Cust 1686/1/2019
Cust 2843/1/2019
Cust 1808/1/2019
Cust 2579/1/2019
Cust 16610/1/2019
Cust 21811/1/2019

 

Calendar Table:

Date_MonhtlyMonthNoYear
1/1/201912019
2/1/201922019
3/1/201932019
4/1/201942019
5/1/201952019
6/1/201962019
7/1/201972019
8/1/201982019
9/1/201992019
10/1/2019102019
11/1/2019112019
12/1/2019122019

 

Now, If I'm going to make a calendar slice and select "4/1/2019" and select Top 2 customer the result will be like on the table below showing only the Top 2 customers based on sales for the month of "4/1/2019" together with the historical data or the past 4 months of data like on the table below.

 

Filter: Let's assume I select "Top 2" on the TopN filter.

Calendar Slicer : I select "4/1/2019"

 

Since the top 2 customer is "Cust 1 and Cust 2" I want to see the result like this.

Result:

CustomerNameMonth YearSales
Cust 11/1/201953
Cust 12/1/201974
Cust 13/1/201945
Cust 14/1/201988
Cust 21/1/201972
Cust 22/1/2019177
Cust 23/1/201984
Cust 24/1/201972

 

It would really appreciated if you can share your solution on here. Thank you.

Note: I am trying to dynamically change this based on the month I select and TopN, but still only shows customer in TopN result with past 4 months of data.

 

Thank you!

HadBen
Frequent Visitor

First you must make a proper date table so you could have the month number associated with the date. 

Seperate the monthyear into Month Name and Year columns, and make a real date out of it. Even something like 1-jan-2019 will be fine. By telling PowerBI what a date is, the program will be able to make a trend and some sense into it.

Then you can connect the two tables and make a trendline, or use the top n filter in the filter pane. 

@HadBen  Thank you for this, I already had a calendar table and customer table. Could you share you solution please.. Kindly see more details below.

 

Here is my sample tables below.

 

Customer Table:

CustomerNameSalesMonthly
Cust 1531/1/2019
Cust 2862/1/2019
Cust 363/1/2019
Cust 4814/1/2019
Cust 5465/1/2019
Cust 1742/1/2019
Cust 2721/1/2019
Cust 3288/1/2019
Cust 4299/1/2019
Cust 12610/1/2019
Cust 2912/1/2019
Cust 3212/1/2019
Cust 4621/1/2019
Cust 5252/1/2019
Cust 1453/1/2019
Cust 1884/1/2019
Cust 2984/1/2019
Cust 1686/1/2019
Cust 2843/1/2019
Cust 1808/1/2019
Cust 2579/1/2019
Cust 16610/1/2019
Cust 21811/1/2019

 

Calendar Table:

Date_MonhtlyMonthNoYear
1/1/201912019
2/1/201922019
3/1/201932019
4/1/201942019
5/1/201952019
6/1/201962019
7/1/201972019
8/1/201982019
9/1/201992019
10/1/2019102019
11/1/2019112019
12/1/2019122019

 

Now, If I'm going to make a calendar slice and select "4/1/2019" and select Top 2 customer the result will be like on the table below showing only the Top 2 customers based on sales for the month of "4/1/2019" together with the historical data or the past 4 months of data like on the table below.

 

Filter: Let's assume I select "Top 2" on the TopN filter.

Calendar Slicer : I select "4/1/2019"

 

Since the top 2 customer is "Cust 1 and Cust 2" I want to see the result like this.

Result:

CustomerNameMonth YearSales
Cust 11/1/201953
Cust 12/1/201974
Cust 13/1/201945
Cust 14/1/201988
Cust 21/1/201972
Cust 22/1/2019177
Cust 23/1/201984
Cust 24/1/201972

 

It would really appreciated if you can share your solution on here. Thank you.

Note: I am trying to dynamically change this based on the month I select and TopN, but still only shows customer in TopN result with past 4 months of data.

 

Thank you!

Anonymous
Not applicable

Hi @markPowerBi 

 

Try below measure,

Rank =
var a =RANKX(ALLSELECTED(Table1[Customer]),[Sales],,DESC,Dense)
var b =SWITCH(SELECTEDVALUE('Parameter Selection'[Number of top]),"1",1,"2",2,"3",3,"4",4,"5",5,"All",99)
return
if(a<=b,1,0)
And add it in visual level filter and set is not 0.
 
I assume sales is your measure.
Note:
You need to create dummy tables parameter selection which is not connected to any other table.
And add 1,2,3,4,5, All
Values in it.
 
Thanks & regards,
Pravin Wattamwar
Linkdin: www.linkedin.com/in/pravin-p-wattamwar
 
If it resolves your problem mark it as solution and give kudoes.

Hi!
Thank you for this. But, its seems this is only to rank customers. Please read my original post what I need is to show also the previous data based on the rank result..
Anonymous
Not applicable

Hi @markPowerBi 

 

As per my understanding you need past three months of data and based on that data you need top N customers.

 

For the same you can create one dummy date slicers where you can accept dates from user.

Let assume it is after date slicer.

SO create one measure to show only last 3 month of data.

 

Filter measure=SUMX(Table,IF(Datediff(Table[Date],min(Date[Date],months)<2,1,0)))

Then add this measure into your table and set is not to 0.

This will show you only past 3 months of data.

(Note: you can make it dyanamic also last N month. create one dummy slicers and take input from user and replace 2 with Selectedvalue(Input)-1)

 

And then apply above ranking logic which i have shared.

 

Thanks & regards,

Pravin Wattamwar

Thank you for your help, but to be more specific I will share to you the sample scenario and output will be.

Here is my sample tables below.

Customer Table:

CustomerNameSalesMonthly
Cust 1531/1/2019
Cust 2862/1/2019
Cust 363/1/2019
Cust 4814/1/2019
Cust 5465/1/2019
Cust 1742/1/2019
Cust 2721/1/2019
Cust 3288/1/2019
Cust 4299/1/2019
Cust 12610/1/2019
Cust 2912/1/2019
Cust 3212/1/2019
Cust 4621/1/2019
Cust 5252/1/2019
Cust 1453/1/2019
Cust 1884/1/2019
Cust 2984/1/2019
Cust 1686/1/2019
Cust 2843/1/2019
Cust 1808/1/2019
Cust 2579/1/2019
Cust 16610/1/2019
Cust 21811/1/2019

 

Calendar Table:

Date_MonhtlyMonthNoYear
1/1/201912019
2/1/201922019
3/1/201932019
4/1/201942019
5/1/201952019
6/1/201962019
7/1/201972019
8/1/201982019
9/1/201992019
10/1/2019102019
11/1/2019112019
12/1/2019122019

 

Given:

1. topN is working (Note: my TopN is based on the sales of the selected month on my calendar filter/slicer)

2. Calender Filter/Slicer 

Now I selected "4/1/2019" on my calendar filter and select Top 2 on my TopN filter. (The result for my TopN below) which is correct. 

CustomerNameSalesMonthly
Cust 1884/1/2019
Cust 2984/1/2019

 

Now the final result should be like on the table below showing only the Top 2 customers together with the historical data or the past 3 or 4 months of data like on the table below. 

 

Since the top 2 customer is "Cust 1 and Cust 2" I want to see the result like this.

Result: sample 1 (Other table)

CustomerNameMonth YearSales
Cust 11/1/201953
Cust 12/1/201974
Cust 13/1/201945
Cust 14/1/201988
Cust 21/1/201972
Cust 22/1/2019177
Cust 23/1/201984
Cust 24/1/201972

 

Sample 2:

I select "5/1/2019", and Select Top2 on my TopN filter the result should be like the table below since for the month of May "5/1/2019" we only had 1 customer available to get 2 customers

RankX result below:

CustomerNameSalesMonthly
Cust 5465/1/2019

 

Final Result for Sample 2: (Other Table)

Since we only had 1 customer Rankx for the month of May "05/1/2019" this is the only customer will appear on the table result together with the historical data or past 3 or 4 months of data sales. Result below.

 

CustomerNameSalesMonthly
Cust 5252/1/2019
Cust 5465/1/2019

 

As you can see on the sample 2 result only customer 5 and for the historical data only show 2 differents months of data because the customer sales has no data for March and April 2019.

 

Note: This is the most important that I wanted to achieve here is whatever the result in my RankX in order to get the Customer List or Customer TopN those specific customer will appear on the table result with historical data.

 

It would really appreciate if you can share your solution on here. Thank you.

Thank you!

Anonymous
Not applicable

Hi @markPowerBi 

 

Please check solution here. https://community.powerbi.com/t5/Desktop/Selected-Value-is-not-working-with-calculateTable/m-p/88879... 

Create Rank column in Customer Table

Rank = RANKX(FILTER(Customer,Customer[Monthly]=EARLIER(Customer[Monthly])),Customer[Sales],,,Dense)

 

Create YEar month column in Calendar date table.

 

I have shown sales value as Column instead you can have measure so that it will return 177 as sum for Customer 2 in second month.

And Rank formula,

 

Thanks & regards,

Pravin Wattamwar

https://www.linkedin.com/in/pravin-p-wattamwar/

 

Hi,

To keep things simple, why do you not want to select 4 months (instead of selecting only 1 month)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Because I want to show customers trends based on the ranked result, so dynamically if I filter any dates or months it will always show past 4 months of the selected month this will also apply in other visuals thats why I want to select just 1 month..

I will also adding some insight to that.

Thank you!

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,
That may help, but unfortunately Im trying to lessen my slicer or filter on my dashboard and what you shared was only part of my concern here.. I really need to connect that and show only customers in TopN I selected.

Thank you..

Hi,

See if my solution here helps.  You may download my PBI file from here.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

I just downloaded your file and check your solution, the past past 4 months of data if perfectly fine but the the overall output is incorrect.

 

 

 

example if I select "May" the output should be the Customer 5 only with past 4 months of data, since if you check the data source for the month of May only customer 5 has sales for that month.

 

Same with other month it seems the rankings not work properly.

 

Note: the output should be based on the the ranking of the selected month by customer with the past 4 months. If the customer is not in the ranking those customer will not appear on the table.

 

Let's set our TopN rank to 2 or Top 2 customer only, so only 2 customers will always shows in the table.

 

Thank you.

Hi,

I am unable to solve it.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
markPowerBi
Frequent Visitor

Is there anyone who could help me on this or even give some tip?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors