Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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]
)
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]
)
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |