topic Re: Ranking Months Based on Date Slicer selected date. in DAX Commands and Tips

Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Fri, 13 Jan 2023 08:59:51 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Fri, 13 Jan 2023 09:03:52 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Fri, 13 Jan 2023 09:12:56 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Fri, 13 Jan 2023 10:42:11 GMT

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.

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Fri, 13 Jan 2023 15:04:21 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Fri, 13 Jan 2023 16:00:14 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Sat, 14 Jan 2023 05:52:03 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Sat, 14 Jan 2023 11:45:24 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Mon, 16 Jan 2023 06:03:52 GMT

Thank you Anonymous 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, 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 🙂

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Mon, 16 Jan 2023 17:03:48 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Tue, 17 Jan 2023 04:32:02 GMT

Hi, Anonymous ,

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 🙂

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Tue, 17 Jan 2023 21:27:31 GMT

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.

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Wed, 18 Jan 2023 04:46:42 GMT

Hi Anonymous ,

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 🙂

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Thu, 19 Jan 2023 00:07:33 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Thu, 19 Jan 2023 04:27:56 GMT

Hi Anonymous ,

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 🙂

Re: Ranking Months Based on Date Slicer selected date.

Anonymous — Thu, 19 Jan 2023 14:50:24 GMT

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?

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Thu, 19 Jan 2023 14:57:13 GMT

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

Re: Ranking Months Based on Date Slicer selected date.

Piyush_Ghungrud — Wed, 01 Feb 2023 09:12:08 GMT

Hi Anonymous & 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 🙂