Set Analysis Exclusion in Max Function in Same Dimension

[Anonymous]

Hi,

I'm new to QV and I've been struggling with this problem for two days now and I hope someone can help me quickly.

I have the following expression in one of my charts:

Count(If(Aggr(NODISTINCT max( [Report Date]), [Project ID]) = [Report Date], if([Project Health]='Green',[Project ID])))

The context here is that I am attempting to count the number of projects with "Green" status based on the last reported status.

So for example, given the following data:

Report Date	Project ID	Status
Jan 2017	A	Yellow
Feb 2017	A	Green
Mar 2017	A	Yellow
Jan 2017	B	Green
Feb 2017	B	Green

And having Report Date as my dimension in the chart, the chart should have something like

Report Date	Green Count
Jan 2017	1 (*because only Project B reported Green)
Feb 2017	2 (*because both Project A and B reported Green)
Mar 2017	1 (*because the last value of Project B was Green while the last reported value of Project A is Yellow)

My problem is that the max([Report Date]) part is evaluating the maximum report date for each Project ID and not only up to the value in the dimension so what I'm getting instead is

Report Date	Green Count
Jan 2017	0
Feb 2017	1
Mar 2017	1

What I want to do:

I want to modify the Max function expression to only report the maximum value of [Report Date] that is equal to or less than the dimension value of [Report Date] being evaluated in the chart, but I don't know how to do this via set analysis or anything else.

Please help.