Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare Top N this month with Top N last month

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.

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

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
ssugar
Resolver III
Resolver III

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
Not applicable

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:

 

ClientStart dateNumber of employees using our system this month% of employees using our system
Client 101/05/2018100075%
Client 201/06/201850050%
Client 301/07/20184065%
Client 401/08/201860060%
Client 501/09/20188050%
Client 615/09/2018100075%
Client 730/09/201840040%
Client 801/10/20184025%
Client 915/10/201860060%
Client 1001/11/20188050%

 

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):

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 915/10/201860060%New Entry
Client 801/10/20184025%New Entry
Client 730/09/201840040%Mover down
Client 615/09/2018100075%Mover down
Client 501/09/20188050%Mover down

 

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

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 101/05/2018100075%Non mover
Client 615/09/2018100075%Mover up
Client 401/08/201860060%Mover down
Client 915/10/201860060%New Entry
Client 201/06/201850050%Mover down

 

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

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 101/05/2018100075%Non mover
Client 615/09/2018100075%Mover up
Client 301/07/20184065%Non mover
Client 401/08/201860060%Mover down
Client 915/10/201860060%New Entry

 

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

 

Raw data @ 30/09/18:

ClientStart dateNumber of employees using our system this month% of employees using our system
Client 101/05/201875056%
Client 201/06/201840040%
Client 301/07/20183049%
Client 401/08/201850050%
Client 501/09/20185031%
Client 615/09/201850038%
Client 730/09/201820020%

 

 

which would form the following league tables:

 

Top 5 Newest Clients @ 30/09/18:

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 730/09/201820020%New Entry
Client 615/09/201850038%New Entry
Client 501/09/20185031%New Entry
Client 401/08/201850050%Mover down
Client 301/07/20183049%Mover down

 

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

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 101/05/201875056%Non mover
Client 401/08/201850050%Non mover
Client 615/09/201850038%New Entry
Client 201/06/201840040%Mover down
Client 730/09/201820020%New Entry

 

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

 

ClientStart dateNumber of employees using our system this month% of employees using our systemChanges since last month
Client 101/05/201875056%Non mover
Client 401/08/201850050%Non mover
Client 301/07/20183049%Mover up
Client 201/06/201840040%Mover down
Client 615/09/201850038%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.

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. 

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous,

 

are you able to post some dummy raw data?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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