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

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

ProductLaunch DateMonthSales
AAug-20Sep-2010
AAug-20Oct-208
AAug-20Dec-205
BMay-20Aug-204
BMay-20Sep-209
BMay-20Oct-206
CJan-21Feb-218
CJan-21Mar-215
CJan-21May-216

 

I was thinking if it is possible to Create a Dynamic Rank , based on the order of months, given the date selected.

 Date SelectedDec-20  
     
Product  Sales  Launch Date  Month  RANK  
A10Aug-20Sep-201
A8Aug-20Oct-202
A0Aug-20Nov-203
A5Aug-20Dec-204
B0May-20Jun-201
B0May-20Jul-202
B4May-20Aug-203
B9May-20Sep-204
B6May-20Oct-205

 

if, the data between launch and month is not there, then it should take the date in order and also rank it.


Thank you 

Piyush_Ghungrud_0-1673600356159.png

 

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

eliasayy_0-1673696345173.png

 

example 2:

eliasayy_1-1673696393891.png

 

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


View solution in original post

17 REPLIES 17
eliasayy
Impactful Individual
Impactful Individual

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

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 🙂

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.

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

eliasayy_0-1673622239955.png

 

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

 

eliasayy
Impactful Individual
Impactful Individual

@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

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 

Piyush_Ghungrud_0-1673675487247.png

 

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:

eliasayy_0-1673696345173.png

 

example 2:

eliasayy_1-1673696393891.png

 

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


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
AOct-202082
BJul-202002
CMar-202152

 

Thank you 🙂

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 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 🙂

 

 

 

 

 

 

 

 

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

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 🙂

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.

 

 

Hi @eliasayy ,

I have tried the way you mentioned, below is the visual that I am getting
Screenshot_20230118_100913.pngCan 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 🙂

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

Hi @eliasayy ,
Screenshot_20230119_095430.png

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 🙂

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?

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

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.

Top Solution Authors