Forum Discussion
Return max date given criteria
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?
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.
5 Replies
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.