Rank table over 3 different months

AndyTrezise,

1.Create Year table containing year values(2016,2017…), create Month table containing month values(1,2,3…12). Please note that there is no relationship among the three tables(Year, Month and UTILISATION table).





2. Create the following calculated columns in the UTILISATION table.

Date = DATE(UTILISATION[Year],UTILISATION[Month],1)

RankColumn = RANKX(FILTER(UTILISATION,UTILISATION[Year]=EARLIER(UTILISATION[Year])&&UTILISATION[Month]=EARLIER(UTILISATION[Month])),UTILISATION[Utilisation],,DESC,Dense)

3. Create the following measures in UTILISATION table.

checkmonth = 
var Ldate = date(max('Year'[Year]),max('Month'[Month]),1) //Last date

var Fdate = EDATE(Ldate,-2) //First date

return

 if(min('UTILISATION'[Date])<fdate,
         blank(),
         if(min('UTILISATION'[Date])>Ldate,
                   blank(),
                   1))

Average = 
var Ldate = date(max('Year'[Year]),max('Month'[Month]),1) //Last date

var Fdate = EDATE(Ldate,-2) //First date

return

 (CALCULATE(SUM(UTILISATION[Utilisation]),DATESBETWEEN(UTILISATION[Date],Fdate,Ldate)))/3

Rank = RANKX(ALLSELECTED(UTILISATION[Employee]),[Average],,DESC,Dense)

4. Create a table visual as follows, drag checkmonth  to visual level filters and set its value to 1. The second table visual doesn't require to set checkmonth.