Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Rank and show Past 3 months sales

‎11-21-2019 04:20 AM
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..
Labels:
Message 1 of 18
2,816 Views
0
17 REPLIES 17
Shamsbutt
Helper II
Helper II

‎12-31-2019 09:32 AM

Hello @Anonymous ,

 

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-power-bi.html

 

and Follow this link to select top N customers by sales

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

 

Thanks

Reagrds

Adeel

Message 18 of 18
2,058 Views
0
Anonymous
Not applicable

‎12-15-2019 03:47 PM

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!

Message 17 of 18
2,660 Views
0
HadBen
Frequent Visitor

‎12-15-2019 01:59 AM

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. 

Message 3 of 18
2,681 Views
0
Anonymous
Not applicable
In response to HadBen

‎12-15-2019 03:52 PM

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

Message 4 of 18
2,660 Views
0
Anonymous
Not applicable
In response to Anonymous

‎12-26-2019 01:12 AM

Hi @Anonymous 

 

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.
Message 12 of 18
2,086 Views
0
Anonymous
Not applicable
In response to Anonymous

‎12-30-2019 08:14 PM
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..
Message 13 of 18
2,081 Views
0
Anonymous
Not applicable
In response to Anonymous

‎12-30-2019 10:59 PM

Hi @Anonymous 

 

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

Message 14 of 18
2,070 Views
0
Anonymous
Not applicable
In response to Anonymous

‎01-01-2020 05:06 PM

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!

Message 15 of 18
2,046 Views
0
Anonymous
Not applicable
In response to Anonymous

‎01-03-2020 01:05 AM

Hi @Anonymous 

 

Please check solution here. https://community.powerbi.com/t5/Desktop/Selected-Value-is-not-working-with-calculateTable/m-p/888790/highlight/false#M426070 

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/

 
Message 16 of 18
2,024 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎12-18-2019 03:25 PM

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/
Message 5 of 18
2,617 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎12-19-2019 04:32 AM
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!
Message 6 of 18
2,588 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎12-19-2019 03:56 PM

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/
Message 7 of 18
2,569 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎12-20-2019 03:45 AM
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..
Message 8 of 18
2,541 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎12-20-2019 06:38 PM

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/
Message 9 of 18
2,530 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎12-23-2019 05:43 AM

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.

Message 10 of 18
2,103 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎12-23-2019 04:20 PM

Hi,

I am unable to solve it.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 11 of 18
2,094 Views
0
Anonymous
Not applicable

‎12-14-2019 10:47 AM
Is there anyone who could help me on this or even give some tip?
Message 2 of 18
2,705 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All