Compare Top N this month with Top N last month

[Anonymous]

Hi,

I want to be able to compare a Top N table with this month's data to a Top N table with last month's data, showing the new entries into the table, and movers up or down.

This is an example of the sort of table I mean, e.g. Top 3 clients ranked by newest start date (at 31/10/18):

Client	Start date	Number of employees using our system this month	% of employees using our system
Client X	20/10/18	500	25%
Client Y	30/09/18	50	50%
Client Z	01/10/18	100	75%

I achieved this table by using the Top N Filter in the Visualisation panel.

I have more tables showing the data ranked by different columns, e.g. Top 3 clients ranked by % of employees using system (at 31/10/18):

Client	Start date	Number of employees using our system this month	% of employees using our system
Client V	14/07/18	400	90%
Client Z	25/08/18	100	75%
Client W	01/05/18	500	60%

I want to add a new column to the tables which says whether the client is a new entry into the table since last month or has moved up or down since last month. E.g. in the first table, client X would be a new entry since the previous month.

However I am unsure how to approach this. Any help is gratefully received - thank you.

[ssugar]

As a start, I'm thinking you should use the RANKX dax function in a custom column instead of using the TopN visualization filter so you can have the rank of the dates in your table.  Something like this:

DateRank = RANKX(Table1, Table1[Start date]) 

Then you could have a visual filter on DateRank that only shows the Top3.

To show which dates are new (e.g. start date is in last month or this month), you could look at another custom column like this:

NewDate = IF(DATEDIFF(Table1[Start date].[Date], TODAY(), MONTH) < 2, 1, 0)

You could then use conditional formatting to highlight clients that were new in green.

More sample data would help.

[Anonymous]

Thanks for your reply @ssugar, but I would like the NewDate column to show the most recent Start Dates, not necessarily the ones where the Start Date is in this month or last month. E.g. if the most recent start date was 01/07/18 and we haven't had any new clients join since then, I would still like the most recent date in the table to be 01/07/18.

I don't think I have explained myself properly - here is some more sample data. @LivioLanzo

Raw data @ today:

Client	Start date	Number of employees using our system this month	% of employees using our system
Client 1	01/05/2018	1000	75%
Client 2	01/06/2018	500	50%
Client 3	01/07/2018	40	65%
Client 4	01/08/2018	600	60%
Client 5	01/09/2018	80	50%
Client 6	15/09/2018	1000	75%
Client 7	30/09/2018	400	40%
Client 8	01/10/2018	40	25%
Client 9	15/10/2018	600	60%
Client 10	01/11/2018	80	50%

I would like to be able to create League tables for the month end based on the 3 final columns like the following:

Top 5 Newest Clients @ 31/10/18 (NB this would not include Client 10 as it was not added until 01/11/18):

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 9	15/10/2018	600	60%	New Entry
Client 8	01/10/2018	40	25%	New Entry
Client 7	30/09/2018	400	40%	Mover down
Client 6	15/09/2018	1000	75%	Mover down
Client 5	01/09/2018	80	50%	Mover down

Top 5 Clients by Number of Employees Using System @ 31/10/18:

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 1	01/05/2018	1000	75%	Non mover
Client 6	15/09/2018	1000	75%	Mover up
Client 4	01/08/2018	600	60%	Mover down
Client 9	15/10/2018	600	60%	New Entry
Client 2	01/06/2018	500	50%	Mover down

Top 5 Clients by % of Employees Using System @ 31/10/18:

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 1	01/05/2018	1000	75%	Non mover
Client 6	15/09/2018	1000	75%	Mover up
Client 3	01/07/2018	40	65%	Non mover
Client 4	01/08/2018	600	60%	Mover down
Client 9	15/10/2018	600	60%	New Entry

NB The raw data at the end of the previous month would be different e.g.:

Raw data @ 30/09/18:

Client	Start date	Number of employees using our system this month	% of employees using our system
Client 1	01/05/2018	750	56%
Client 2	01/06/2018	400	40%
Client 3	01/07/2018	30	49%
Client 4	01/08/2018	500	50%
Client 5	01/09/2018	50	31%
Client 6	15/09/2018	500	38%
Client 7	30/09/2018	200	20%

which would form the following league tables:

Top 5 Newest Clients @ 30/09/18:

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 7	30/09/2018	200	20%	New Entry
Client 6	15/09/2018	500	38%	New Entry
Client 5	01/09/2018	50	31%	New Entry
Client 4	01/08/2018	500	50%	Mover down
Client 3	01/07/2018	30	49%	Mover down

Top 5 Clients by Number of Employees Using System @ 30/09/18:

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 1	01/05/2018	750	56%	Non mover
Client 4	01/08/2018	500	50%	Non mover
Client 6	15/09/2018	500	38%	New Entry
Client 2	01/06/2018	400	40%	Mover down
Client 7	30/09/2018	200	20%	New Entry

Top 5 Clients by % of Employees Using System @ 30/09/18:

Client	Start date	Number of employees using our system this month	% of employees using our system	Changes since last month
Client 1	01/05/2018	750	56%	Non mover
Client 4	01/08/2018	500	50%	Non mover
Client 3	01/07/2018	30	49%	Mover up
Client 2	01/06/2018	400	40%	Mover down
Client 6	15/09/2018	500	38%	New Entry

I don't know how to add in this final column to the tables and that is what I am asking for help on.

I hope that makes sense but please let me know if not  - many thanks in advance for any help you can give me.

[LivioLanzo · Accepted solution]

Hi @Anonymous,

You can download my file: https://1drv.ms/u/s!AiiWkkwHZChHjzu9uSQ7lZXDaqXY

Your data consists of snapshots taken at each month end, so i added a new column containing the snapshot date and added a 3 calculated columns where ranking is pre computed to speed up the calculation. 

[LivioLanzo]

Hi @Anonymous,

are you able to post some dummy raw data?