Forum Discussion
Power BI : Rank based on date Month wise
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
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
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. :smileyhappy:
Regards
6 Replies
- Phil_Seamark
Microsoft Employee
Hi srajeshnkl
Any chance you can post some dummy data including what it should look like with the RANK based measure.
- 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
