RANKX by Year and Month

[Anonymous]

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)

[Anonymous]

@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.

[amitchandak · Accepted 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)