Return max date given criteria

[Anonymous]

Hello,

I have a table of data that look like the below:

Dates	Area	To display?
01-Jan-20	1	No
02-Jan-20	1	No
03-Jan-20	1	No
04-Jan-20	1	No
05-Jan-20	1	Yes
06-Jan-20	1	No
07-Jan-20	1	No
08-Jan-20	1	No
09-Jan-20	1	Yes
01-Jan-20	2	No
02-Jan-20	2	No
03-Jan-20	2	Yes
04-Jan-20	2	No
05-Jan-20	2	No
06-Jan-20	2	Yes
07-Jan-20	2	No
08-Jan-20	2	No
09-Jan-20	2	No
01-Jan-20	3	Yes
02-Jan-20	3	Yes
03-Jan-20	3	No
04-Jan-20	3	No
05-Jan-20	3	No
06-Jan-20	3	No
07-Jan-20	3	No
08-Jan-20	3	No
09-Jan-20	3	No

I want to create a table so it returns the most recent date with a "Yes". The resulting table should look like this:

Area	Yes
1	09-Jan-20
2	06-Jan-20
3	02-Jan-20

How can I accomplish this?

[Icey · Accepted solution]

Hi @Anonymous ,

How about this?

MAX_DATE =
CALCULATE (
    MAX ( YESTABLE[Dates] ),
    ALLEXCEPT ( GEOGRAPHY, GEOGRAPHY[AREA] ),
    YESTABLE[To display] = "Yes"
)

If it doesn't work, please share us some sample data of your two tables, removing sensitive information.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

I would use

MAX_DATE = CALCULATE(MAX(table[Dates]),FILTER(ALLEXCEPT(table,table[Area]),table[To display] = "Yes")

and plot it using matrix

[Anonymous]

Thank you. My issue is that Table[area] isn't part of the Table. "Area" comes from another table and is brought here through a relationship. 

[Anonymous]

HI @Anonymous 

In that case you need to create a relationship between both tables.

[Anonymous]

The relationship has already been created, so how should I re-write this part of the code?

FILTER(ALLEXCEPT(YESTABLE, GEOGRAPHY[AREA]) doesn't seem to work.

FILTER(ALLEXCEPT(table,table[Area]