Rankx top 20 results for prior 3 months

[Marielle_P]

Hi all,

I need to create an overview of the top 20 for claims incurred for claims opened in the last 3 calendar months.

I created the following DAX:

Top 20 Claim incurred = 

VAR ClaimRank = RankX(All(Claim), [Claim incurred], , DESC

VAR Enddate = EOMonth(Today(),-1

VAR Startdate = EDate(EOMonth(Today(),-1),-3)+1

Return

IF(ClaimRank<=20 [Claim incurred] , if(MAX(Claim[Opened date])>=Startdate && MAX (Claim[Opened date])<= Enddate), [Claim incurred], BLANK ()))

But the report is not showing the correct results and I also can't add any data of other tables to the report (i.e. "client" which is is another table.

Are you able to support me? Thanks

[amitchandak · Accepted solution]

@Marielle_P , Create a measure like

Rolling 3 = CALCULATE([Claim incurred],DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-3,MONTH))

///or 
Rolling 3 = CALCULATE([Claim incurred],DATESINPERIOD('Date'[Date ],eomonth(today(),-1),-3,MONTH))

then try rank on it, you can use the visual level filter

You can also use topN, refer to my video for that - https://www.youtube.com/watch?v=QIVEFp-QiOk

Rank = RankX(All(Claim), [Rolling 3], , DESC)