Forum Discussion
Helper IRanking Months Based on Date Slicer selected date.
Hi Community,
I have been wondering if we can create a Ranking Till Dates selected in the Slicer.
Here I have a data which has product, launch date, month and sales.
| Product | Launch Date | Month | Sales |
| A | Aug-20 | Sep-20 | 10 |
| A | Aug-20 | Oct-20 | 8 |
| A | Aug-20 | Dec-20 | 5 |
| B | May-20 | Aug-20 | 4 |
| B | May-20 | Sep-20 | 9 |
| B | May-20 | Oct-20 | 6 |
| C | Jan-21 | Feb-21 | 8 |
| C | Jan-21 | Mar-21 | 5 |
| C | Jan-21 | May-21 | 6 |
I was thinking if it is possible to Create a Dynamic Rank , based on the order of months, given the date selected.
| Date Selected | Dec-20 | |||
| Product | Sales | Launch Date | Month | RANK |
| A | 10 | Aug-20 | Sep-20 | 1 |
| A | 8 | Aug-20 | Oct-20 | 2 |
| A | 0 | Aug-20 | Nov-20 | 3 |
| A | 5 | Aug-20 | Dec-20 | 4 |
| B | 0 | May-20 | Jun-20 | 1 |
| B | 0 | May-20 | Jul-20 | 2 |
| B | 4 | May-20 | Aug-20 | 3 |
| B | 9 | May-20 | Sep-20 | 4 |
| B | 6 | May-20 | Oct-20 | 5 |
if, the data between launch and month is not there, then it should take the date in order and also rank it.
Thank you
hello Piyush_Ghungrud ,
please use this new measure:Index expected result: = VAR _startdate =[Begin] VAR _monthyearbyproducttable = CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Table'[Year-Month], 'Table'[Sort] ), FILTER ( ALL('Table'), 'Table'[Date] >= _startdate ) ) RETURN IF ( NOT ISBLANK ( [Sales Measure] ) && HASONEVALUE ( 'Table'[Year-Month] ), COUNTROWS ( WINDOW ( 1, ABS, 0, REL, _monthyearbyproducttable, ORDERBY ( 'Table'[Sort], ASC ) ) ) )example 1:
example 2:
here is the powerbi file: https://1drv.ms/u/s!Ag9tIyk2ofNRjmhuYW0Ig0PdVG-9?e=pREtpE
if it solved it please accept this as a solution and thank you Jihwan_Kim for the help
17 Replies
Hello Piyush_Ghungrud i didnt quiet understand the condition of ranking, if you select december 20 on what basis should it rank?
Piyush_GhungrudThank you Anonymous for reaching me,
I want to rank months from the launch date of the product till the selected date.
As you can see, the selected month is Dec 20, till Dec 20 whatever product has launched has been displayed and ranking given to the months(Please note: Between Launch Date and Month, there could be missing Months data, but it should fill the missing months and rank those months also).
Please, let me know for further information.
Thank you 🙂- Piyush_Ghungrud
Hi Anonymous ,
In addition to this, The different products can have the same ranks, as the ranking is based on months and the distinct product is selected.