Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

 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

1 ACCEPTED SOLUTION
Impactful Individual

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 17
Impactful Individual

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

Helper I

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 🙂

Helper I

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.

Impactful Individual

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

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

Helper I

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

Impactful Individual

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

Helper I

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 🙂

Helper I

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 🙂

Impactful Individual

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

Helper I

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 🙂

Impactful Individual

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.

Helper I

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 🙂

Impactful Individual

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

Helper I

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 🙂

Impactful Individual

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?

Helper I

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors