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 want to RANK my data according to their respective year and month. Every date that falls in the same year and month, must be in the same group. There are no other criterias needed. Just to rank it and I'm trying to do the syntax below but still doesn't work
RANKX(FILTER(Product,EARLIER([ReceiveDate].[Year])=YEAR([ReceiveDate])&&EARLIER([ReceiveDate].[Month])=[ReceiveDate].[Month]),[Id],,DESC)
Solved! Go to Solution.
@Anonymous , if want to have the same rank for the dates in the month then you need to have ranked on the month year.
Better create month year and rank on it
example
Month Year = FORMAT([Date],"mmm-yyyy")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
@Anonymous
Please refrain from using the auto-generated date hierarchies which are meant to be used only in the simplest of models for quick prototyping and NOTHING ELSE. Create your own calendar with all attributes needed. Connect all fact tables to this calendar dimension table instead of having hierarchies in each of the date columns in your fact tables. This is Best Practice which reduces the model bloat and lets you do calculations in a simple and predictable way. As mentioned by @amitchandak, you should create a YearMonth column of the type YYYYMM (integer) and then rank on it.
@Anonymous , if want to have the same rank for the dates in the month then you need to have ranked on the month year.
Better create month year and rank on it
example
Month Year = FORMAT([Date],"mmm-yyyy")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |