Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to get a rank by date, starting with the most recent date, and only for dates that have values.
Example below. In the example I have a number of date, some of them have quantites. For those that do have a quantity I would like to rank the date in descending order with the most recent date being rank 1.
I do not know whether this should be a measure or a calculated column, but I do know that the values will change based on the filters that are applied, such as the customer.
Any help is much appreciated.
Date | Quantity | Rank |
01/01/2020 | ||
02/01/2020 | ||
03/01/2020 | 10 | 6 |
04/01/2020 | ||
05/01/2020 | 500 | 5 |
06/01/2020 | ||
07/01/2020 | ||
08/01/2020 | ||
09/01/2020 | 3 | 4 |
10/01/2020 | ||
11/01/2020 | ||
12/01/2020 | 55 | 3 |
13/01/2020 | 43 | 2 |
14/01/2020 | 101 | 1 |
15/01/2020 | ||
16/01/2020 |
Solved! Go to Solution.
Here is one measure expression to try. Change DateRank to the actual name of your table.
Rank if Qty =
VAR vThisRank =
RANKX (
FILTER (
ALLSELECTED (
DateRank[Date],
DateRank[Quantity]
),
DateRank[Quantity] > 0
),
CALCULATE (
MAX ( DateRank[Date] )
),
,
DESC
)
RETURN
IF (
SUM ( DateRank[Quantity] ) > 0,
vThisRank,
BLANK ()
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one measure expression to try. Change DateRank to the actual name of your table.
Rank if Qty =
VAR vThisRank =
RANKX (
FILTER (
ALLSELECTED (
DateRank[Date],
DateRank[Quantity]
),
DateRank[Quantity] > 0
),
CALCULATE (
MAX ( DateRank[Date] )
),
,
DESC
)
RETURN
IF (
SUM ( DateRank[Quantity] ) > 0,
vThisRank,
BLANK ()
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Fantastic. That did the trick, thank you very much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |