topic Re: Rank and show Past 3 months sales in DAX Commands and Tips

Rank and show Past 3 months sales

Anonymous — Thu, 21 Nov 2019 12:20:41 GMT

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..

Re: Rank and show Past 3 months sales

Anonymous — Sat, 14 Dec 2019 18:47:15 GMT

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

Re: Rank and show Past 3 months sales

HadBen — Sun, 15 Dec 2019 09:59:45 GMT

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.

Re: Rank and show Past 3 months sales

Anonymous — Sun, 15 Dec 2019 23:47:40 GMT

Here is my sample tables below.

Customer Table:

CustomerName	Sales	Monthly
Cust 1	53	1/1/2019
Cust 2	86	2/1/2019
Cust 3	6	3/1/2019
Cust 4	81	4/1/2019
Cust 5	46	5/1/2019
Cust 1	74	2/1/2019
Cust 2	72	1/1/2019
Cust 3	28	8/1/2019
Cust 4	29	9/1/2019
Cust 1	26	10/1/2019
Cust 2	91	2/1/2019
Cust 3	2	12/1/2019
Cust 4	62	1/1/2019
Cust 5	25	2/1/2019
Cust 1	45	3/1/2019
Cust 1	88	4/1/2019
Cust 2	98	4/1/2019
Cust 1	68	6/1/2019
Cust 2	84	3/1/2019
Cust 1	80	8/1/2019
Cust 2	57	9/1/2019
Cust 1	66	10/1/2019
Cust 2	18	11/1/2019

Calendar Table:

Date_Monhtly	MonthNo	Year
1/1/2019	1	2019
2/1/2019	2	2019
3/1/2019	3	2019
4/1/2019	4	2019
5/1/2019	5	2019
6/1/2019	6	2019
7/1/2019	7	2019
8/1/2019	8	2019
9/1/2019	9	2019
10/1/2019	10	2019
11/1/2019	11	2019
12/1/2019	12	2019

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:

CustomerName	Month Year	Sales
Cust 1	1/1/2019	53
Cust 1	2/1/2019	74
Cust 1	3/1/2019	45
Cust 1	4/1/2019	88
Cust 2	1/1/2019	72
Cust 2	2/1/2019	177
Cust 2	3/1/2019	84
Cust 2	4/1/2019	72

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!

Re: Rank and show Past 3 months sales

Anonymous — Sun, 15 Dec 2019 23:52:00 GMT

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:

CustomerName	Sales	Monthly
Cust 1	53	1/1/2019
Cust 2	86	2/1/2019
Cust 3	6	3/1/2019
Cust 4	81	4/1/2019
Cust 5	46	5/1/2019
Cust 1	74	2/1/2019
Cust 2	72	1/1/2019
Cust 3	28	8/1/2019
Cust 4	29	9/1/2019
Cust 1	26	10/1/2019
Cust 2	91	2/1/2019
Cust 3	2	12/1/2019
Cust 4	62	1/1/2019
Cust 5	25	2/1/2019
Cust 1	45	3/1/2019
Cust 1	88	4/1/2019
Cust 2	98	4/1/2019
Cust 1	68	6/1/2019
Cust 2	84	3/1/2019
Cust 1	80	8/1/2019
Cust 2	57	9/1/2019
Cust 1	66	10/1/2019
Cust 2	18	11/1/2019

Calendar Table:

Date_Monhtly	MonthNo	Year
1/1/2019	1	2019
2/1/2019	2	2019
3/1/2019	3	2019
4/1/2019	4	2019
5/1/2019	5	2019
6/1/2019	6	2019
7/1/2019	7	2019
8/1/2019	8	2019
9/1/2019	9	2019
10/1/2019	10	2019
11/1/2019	11	2019
12/1/2019	12	2019

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:

CustomerName	Month Year	Sales
Cust 1	1/1/2019	53
Cust 1	2/1/2019	74
Cust 1	3/1/2019	45
Cust 1	4/1/2019	88
Cust 2	1/1/2019	72
Cust 2	2/1/2019	177
Cust 2	3/1/2019	84
Cust 2	4/1/2019	72

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!

Re: Rank and show Past 3 months sales

Ashish_Mathur — Wed, 18 Dec 2019 23:25:23 GMT

Hi,

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

Re: Rank and show Past 3 months sales

Anonymous — Thu, 19 Dec 2019 12:34:22 GMT

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!

Re: Rank and show Past 3 months sales

Ashish_Mathur — Thu, 19 Dec 2019 23:56:20 GMT

Hi,

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

Re: Rank and show Past 3 months sales

Anonymous — Fri, 20 Dec 2019 11:45:40 GMT

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..

Re: Rank and show Past 3 months sales

Ashish_Mathur — Sat, 21 Dec 2019 02:38:04 GMT

Hi,

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

Re: Rank and show Past 3 months sales

Anonymous — Mon, 23 Dec 2019 13:49:10 GMT

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.

Re: Rank and show Past 3 months sales

Ashish_Mathur — Tue, 24 Dec 2019 00:20:14 GMT

Hi,

I am unable to solve it.

Re: Rank and show Past 3 months sales

Anonymous — Thu, 26 Dec 2019 09:12:47 GMT

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.

Re: Rank and show Past 3 months sales

Anonymous — Tue, 31 Dec 2019 04:14:03 GMT

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..

Re: Rank and show Past 3 months sales

Anonymous — Tue, 31 Dec 2019 06:59:42 GMT

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

Re: Rank and show Past 3 months sales

Shamsbutt — Tue, 31 Dec 2019 17:32:20 GMT

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

Re: Rank and show Past 3 months sales

Anonymous — Thu, 02 Jan 2020 01:06:01 GMT

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:

CustomerName	Sales	Monthly
Cust 1	53	1/1/2019
Cust 2	86	2/1/2019
Cust 3	6	3/1/2019
Cust 4	81	4/1/2019
Cust 5	46	5/1/2019
Cust 1	74	2/1/2019
Cust 2	72	1/1/2019
Cust 3	28	8/1/2019
Cust 4	29	9/1/2019
Cust 1	26	10/1/2019
Cust 2	91	2/1/2019
Cust 3	2	12/1/2019
Cust 4	62	1/1/2019
Cust 5	25	2/1/2019
Cust 1	45	3/1/2019
Cust 1	88	4/1/2019
Cust 2	98	4/1/2019
Cust 1	68	6/1/2019
Cust 2	84	3/1/2019
Cust 1	80	8/1/2019
Cust 2	57	9/1/2019
Cust 1	66	10/1/2019
Cust 2	18	11/1/2019

Calendar Table:

Date_Monhtly	MonthNo	Year
1/1/2019	1	2019
2/1/2019	2	2019
3/1/2019	3	2019
4/1/2019	4	2019
5/1/2019	5	2019
6/1/2019	6	2019
7/1/2019	7	2019
8/1/2019	8	2019
9/1/2019	9	2019
10/1/2019	10	2019
11/1/2019	11	2019
12/1/2019	12	2019

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.

CustomerName	Sales	Monthly
Cust 1	88	4/1/2019
Cust 2	98	4/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)

CustomerName	Month Year	Sales
Cust 1	1/1/2019	53
Cust 1	2/1/2019	74
Cust 1	3/1/2019	45
Cust 1	4/1/2019	88
Cust 2	1/1/2019	72
Cust 2	2/1/2019	177
Cust 2	3/1/2019	84
Cust 2	4/1/2019	72

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:

CustomerName	Sales	Monthly
Cust 5	46	5/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.

CustomerName	Sales	Monthly
Cust 5	25	2/1/2019
Cust 5	46	5/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!

Re: Rank and show Past 3 months sales

Anonymous — Fri, 03 Jan 2020 09:05:59 GMT

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/