Power BI : Rank based on date Month wise

[srajeshnkl]

Hi Folks,

I have a table with below columns,  (Represents data of idea implementation)

S.No

Name

Idea Name

Implemented Date

The data is for whole year. I want to give Rank to the persons based on the "Implemented Date" for each month.Person implements a idea first on that month will get Rank 1. For example if Person X implements idea on 1-Jan-2017 then X gets rank 1 on January the same way if Person Y implements idea on 1-Feb-2017 then Y gets rank 1 on month Febrary . In this way I want to give rank for all persons on each month. How can I use RankX function in power BI to achieve this?

Please help me.

Below is the example data and Rank should be automatically calculated for each month like below

S.No	Name	Implemented date	Rank
4	Suganya	1-Jan-17	1
5	Vanitha	2-Jan-17	2
6	Sangeetha	4-Jan-17	3
8	Ravikumar	8-Jan-17	4
1	Sathish	12-Jan-17	5
3	Visagan	13-Jan-17	6
9	Ramesh	19-Jan-17	7
2	Rajesh	22-Jan-17	8
10	Harini	25-Jan-17	9
7	Sahana	31-Jan-17	10
12	Rajesh	1-Feb-17	1
16	Sangeetha	1-Feb-17	1
18	Ravikumar	1-Feb-17	1
13	Visagan	15-Feb-17	4
19	Ramesh	15-Feb-17	4
11	Sathish	18-Feb-17	6
14	Suganya	18-Feb-17	6
17	Sahana	18-Feb-17	6
20	Harini	18-Feb-17	6
15	Vanitha	21-Feb-17	10

Regards, 

Rajesh S

[Phil_Seamark · Accepted solution]

Hi @srajeshnkl

You could try adding the following two calcuated columns

MonthID = FORMAT('Table3'[Implemented date],"YYYYMM")

Month = FORMAT(DATE(
            YEAR('Table3'[Implemented date]),
            MONTH('Table3'[Implemented date]),
            1),"MMM YY")

Make sure you hide MonthID and set Month to be sorted by MonthID

Then just drag the new Month column to the Column of a Matrix

[Phil_Seamark]

Hi @srajeshnkl

Any chance you can post some dummy data including what it should look like with the RANK based measure.

[srajeshnkl]

Hi @Phil_Seamark

I have updated original post with example. Please help me.

[Phil_Seamark]

Hi @srajeshnkl

You could try adding the following calculated column to your table.   Just replace where I Table3 with your table name.

New Rank = CALCULATE(
                COUNTROWS('Table3'),
                FILTER('Table3',
                    'Table3'[Implemented date] < EARLIER('Table3'[Implemented date])
                    && YEAR('Table3'[Implemented date]) = YEAR(EARLIER('Table3'[Implemented date]))
                    && MONTH('Table3'[Implemented date]) = MONTH(EARLIER('Table3'[Implemented date]))                       
                    ))+1

[srajeshnkl]

@Phil_Seamark

Thanks for your help. This works fine.

Now I want d to generate a report like below, This report is in another table, I have already configured names in the new table, Corresponding ranks for the particular months to be copied to this new sheet as below, If rank not found in previous table then apply NA or last rank. How to achieve this ?

Name	Jan	Feb
Sathish	5	6
Rajesh	8	1
Karthik	NA	NA
Visagan	6	4
Suganya	1	6
Vanitha	2	10
Sangeetha	3	1
Sahana	10	6
Ravikumar	4	1
Ramesh	7	4
Harini	9	6

[v-ljerr-msft · Accepted solution]

Hi @srajeshnkl,

Have you tried the solution provided by @Phil_Seamark above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Phil_Seamark · Accepted solution]

Hi @srajeshnkl

You could try adding the following two calcuated columns

MonthID = FORMAT('Table3'[Implemented date],"YYYYMM")

Month = FORMAT(DATE(
            YEAR('Table3'[Implemented date]),
            MONTH('Table3'[Implemented date]),
            1),"MMM YY")

Make sure you hide MonthID and set Month to be sorted by MonthID

Then just drag the new Month column to the Column of a Matrix