Highest ranking over all time by month?

[denxx34]

Hello everyone,

can you please help me to solve the following challenge...

In a table I have a Group, Date and Quantity.
Then calculate a rank for the quantity.

What I also need is...
A measure that shows me which highest ranking each group has ever had in relation to past months.

So when I select... show me the year=2022 and month=4, then I see the rank for the groups and at the same time the highest rank that group has ever had.

id	date		qty	group
1	01.01.2021		12	A
2	05.11.2021		3	B
3	31.12.2021		4	B
4	01.01.2022		5	A
5	01.02.2022		23	B
6	31.03.2022		5	C
7	15.04.2022		10	B
8	15.05.2022		78	B
9	15.01.2023		23	C
10	05.01.2022		23	B
11	06.01.2022		19	C
12	15.04.2022		8	C
13	20.04.2022		5	D
14	20.05.2022		1	D
15	20.05.2022		2	B
16	01.01.2021		15	B
17	01.04.2022		9	A
18	20.05.2022		2	A

Thx in advance!

[bcdobbs · Accepted solution]

Have a look at solution in:
Example File 

Created a date table with a start of month date column (formatted as MMMM YYYY):

Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[date] ) )

    RETURN
        ADDCOLUMNS (
            CALENDAR (
                DATE ( EarliestYear, 1, 1 ),
                DATE ( LatestYear, 12, 31 )
            ),
            "Month Year", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )

Created a Group dimension table:

Group = 
    DISTINCT ( Sales[group] )

Main measures:

Total Quantity = SUM ( Sales[qty] )

Selected Ranking = 
VAR Result =
    RANKX (
        ALLSELECTED ( 'Group'[group] ),
        [Total Quantity]
    )

RETURN 
    IF (
        NOT ISBLANK ( [Total Quantity] ),
        Result
    )

Highest Monthly Rank = 
    MINX (
        ALL ( 'Date'[Month Year] ),
        [Selected Ranking]
    )