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.
Hello @Anonymous ,
Please follow this Link to show last N months data based on selection
and Follow this link to select top N customers by sales
https://www.youtube.com/watch?v=SsZseKOgrWQ
Thanks
Reagrds
Adeel
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!
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:
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!
Hi @Anonymous
Try below measure,
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
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!
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/
Hi,
To keep things simple, why do you not want to select 4 months (instead of selecting only 1 month)?
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hi,
See if my solution here helps. You may download my PBI file from here.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |