cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Highest ranking over all time by month?

Hello everyone,

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

1 ACCEPTED SOLUTION
Super User

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
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]
)``````

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Super User

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
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]
)``````

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors