Filter Percentile expression to only evaluate on current month

Question

I have a table&nbsp;Table1Person&nbsp; &nbsp; &nbsp; &nbsp; Date (Month) &nbsp; &nbsp; &nbsp; Score &nbsp; &nbsp; &nbsp; StatusJane &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2016-01-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoodSteven &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2016-01-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BadRichard &nbsp; &nbsp; &nbsp; 2016-01-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BadAlbert &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2016-01-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BadJane &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2016-02-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GoodSteven &nbsp; &nbsp; &nbsp;&nbsp;2016-02-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BadRichard &nbsp; &nbsp; 2016-02-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BadAlbert &nbsp; &nbsp; &nbsp;&nbsp;2016-02-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GoodStatus is derived by the following fomula :IF(Table1[Score]&gt;PERCENTILE.EXC(Table1[Score],0.8),"Good","Bad"))This evaluates the score for the whole dataset, and returns a "Good" if the score is higher then the score of the 80th percentile.My question is: How do I filter the formula above to only evaluate the score to the 80th percentile of the&nbsp;current month?Thanks

v-caliao-msft · Accepted Answer

dinovic,
&nbsp;
Create a calcualted column to return Year and month.YearMonth = FORMAT(Table1[Date],"YYYYMM")Then you can use the expression below to achieve your requirement.Column = IF(Table1[Score]&gt;=CALCULATE(PERCENTILE.EXC(Table1[Score],0.8),ALLEXCEPT(Table1,Table1[YearMonth])),"Godd","Bad")
&nbsp;
Regards,
Charlie Liao

Forum Discussion

Filter Percentile expression to only evaluate on current month

1 Reply

Related content

Expression Evaluate , error

PowerBI Expression Evaluation Thread Delegation

Lazy evaluation

RLS Expression Evaluation Failed - Failed connection

Help!!! Expression Error