above from last date only

[Anonymous]

following Re: set analysis on non-consecutive dates where Sunny kindly helped me out I still have a question...

I am now trying to figure out how many students (and who are they) that are still having  a grade below 5 for  THE LAST 3 consecutive test date (not just 3 consecutive test date in the past)

(I am using Q11)

so in the below I would have hope to see as a result: S1 and S3

Test Date	Name	Score
30/05/2017	S1	4
31/05/2017	S1	4
01/10/2017	S1	1
30/05/2017	S3	4
31/05/2017	S3	3
01/10/2017	S3	4

from:

LOAD * INLINE [
Test Date, Name, Score
01/01/2017, S1, 4
01/01/2017, S2, 10
01/01/2017, S3, 6
17/03/2017, S1, 9
17/03/2017, S2, 7
17/03/2017, S3, 7
03/05/2017, S1, 4
03/05/2017, S2, 1
03/05/2017, S3, 2
30/05/2017, S1, 4
30/05/2017, S2, 1
30/05/2017, S3, 4
31/05/2017, S1, 4
31/05/2017, S2, 1
31/05/2017, S3, 3
01/10/2017, S1, 1
01/10/2017, S2, 10
01/10/2017, S3, 4
];

Many thanks,

Raphael

[Anonymous]

thank you, no sure I follow your question. can you explain again, sorry.

[sunny_talwar]

In the example above, you would want to always see it for 01/10/2017, 31/05/2017, 30/05/2017 or do you expect to select 31/05/2017 and then compare 31/05/2017, 30/05/2017, 17/03/2017?

[Anonymous]

Idealy yes, but guess that it is not really doable?

[sunny_talwar]

It is possible, but will have to be coded in... do you want me to code it or would you like to give it a try?

[Anonymous]

Honestly I right now would not be sure where to start from, so if you could code it , it would be great! thanks

[sunny_talwar]

Try this

=If(Max({<[Test Date] = {"<=$(=Max([Test Date]))"}>}TOTAL <Name> Aggr(If(Only({1}[Test Date]) >= Max(TOTAL <Name> {<[Test Date] = {"<=$(=Max([Test Date]))"}>} [Test Date], 3), Only({<[Test Date] = {"<=$(=Max([Test Date]))"}>} Score)), Name, [Test Date])) < 5,

Only({<[Test Date] = {"<=$(=Max([Test Date]))"}>}Aggr(If(Only({1}[Test Date]) >= Max(TOTAL <Name> {<[Test Date] = {"<=$(=Max([Test Date]))"}>} [Test Date], 3), Only({<[Test Date] = {"<=$(=Max([Test Date]))"}>} Score)), Name, [Test Date])))

[Anonymous]

I am speechless ! thank you

[sunny_talwar]

No problem, please take some time to mark any helpful responses

Best,

Sunny

[Anonymous]

will do, I was not aware of this option, many thanks