Ranking Months Based on Date Slicer selected date.

[Piyush_Ghungrud]

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 

[eliasayy]

Hello @Piyush_Ghungrud i didnt quiet understand the condition of ranking, if you select december 20 on what basis should it rank?

[Piyush_Ghungrud]

Thank you @eliasayy 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 @eliasayy ,

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.

[eliasayy]

hello @Piyush_Ghungrud sorry for late reply i got busy but i reached a place i just have the ranking im stuck on 

as you can see ive reached your desired result i just have to rank it

[eliasayy]

@Piyush_Ghungrud you can find all the steps i made to reach here in the powerbi file im sorry i couldnt find a solution to ranking

powerbi file : https://1drv.ms/u/s!Ag9tIyk2ofNRjmfg4RpeqSmj2utU?e=lujztN

just make sure to change directory in powerquery of the source of Table so you can see the steps

[Piyush_Ghungrud]

Hi @eliasayy , 

Thank you for your efforts. I see you have got the missing months in there, as well, as per post, that's great.
Now, I am working and looking for a way to rank the Months, If you find a solution please let us know.
Thank You 

[eliasayy · Accepted solution]

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

[Piyush_Ghungrud]

Hi @eliasayy  & @Jihwan_Kim ,
from your solution above, is it possible to get any specific index expected result?
Like, I wanted to have only the second(2nd) index expected result value for product A, B, and C
and the expected result would be

Index Expected Result : 2

Product	Year-Month	Sales Measure	Index Expected Result
A	Oct-2020	8	2
B	Jul-2020	0	2
C	Mar-2021	5	2

Thank you 🙂

[Piyush_Ghungrud]

Thank you @eliasayy  for helping me out.

It is what I wanted 🙂
Coming to think of it, is it possible to plot the rankings on X axis of a Line chart?
I was thinking to show the sales of products from their launch date to selected date(It will show the how the product has performed from the date of launch and all products in chart).

adding chart for reference.

Kindly ignore Test in X-axis, It is ranking(represented as month from launch) .

Kindly ignore, the X-axis title.

In X-axis, the rankings shown(represented as months completed by product since launch). This approach involves creating Ranking in the data table, which makes it static(not dyanamic for selecting dates). 

Please, let us know.

Thank you 🙂

[eliasayy]

Hello im not sure i understood 

Correct me if im wrong

You have the y axis the sales measure for each product and on y axis you want everydate that was on the table by product right?

Do you want the sales to be accumulated after the launch date? Like for example product A if we select on date slicer october 2020,

 On launch in august it had 10 sales then after 2 months it had 5 in september and 2 sales in october

So you want the line to be 10 then 15 then 17 respectively or 10 then 5 then 2

[Piyush_Ghungrud]

Hi, @eliasayy ,

On Y-axis it would be sales and X-axis it would be ranking. As you mentioned, the sales, it will not aggregate, it will be 10 then 5 then 2, this way, it will show how the product has performed since launch date.
If you could suggest any other(apart from ranking) way to get upto this result, I would be happy to know. 

Thank you 🙂

[eliasayy]

I think you could try to put the sales measure i gave you in the powerbi and then add products as legend and then on the x-axis , put the year-month because they are already sorted in the table.

[Piyush_Ghungrud]

Hi @eliasayy ,

I have tried the way you mentioned, below is the visual that I am getting
Can we be able to mention the number of months since launch of product in the X-axis(and not the Month-Year) and start it from the '0th' value, so all the products will show emerging from zero values?

Please let me know, I will also check on the same.

Thank you 🙂

[eliasayy]

im sorry but i didnt get it do you mean that you want the first date on the xaxis to be all are 0?

[Piyush_Ghungrud]

Hi @eliasayy ,

Please check this, here the X-axis has started from zero and the values are also zero for all, by doing such, it shows the product just launched at that point.
Please let me know if you need any more details.

Thank you 🙂

[eliasayy]

Oh so you want the results from  the answer i gave you where you add the month-year with the measure but you want the line to go from 0 up to the value on the first date of launch?

[Piyush_Ghungrud]

Hi @eliasayy, the line from zero and on X-axis, should show the index expected result measure.
Thank you 🙂