How to use hook_views_query_alter to catch last DELTA of a multivalue date field?
This can be done with HOOK_views_query_alter(). Add all the fields as normal within the views UI, including filtering on the date field value. Make sure the entity ID is in there (in your case, nid is in the query) and the date's delta field.

Next, add a WHERE clause on the delta in hook_views_query_alter() using add_where_expression():

function MODULE_views_query_alter(&amp;$view, &amp;$query) {
  // Check for correct view
  if ($view-&gt;name == 'view_name' &amp;&amp; $view-&gt;current_display == 'display_name') {
    $query-&gt;add_where_expression(0, 'field_data_field_period.delta = (SELECT MAX(field_data_field_period.delta) from field_data_field_period WHERE entity_id = nid)');
  }
}


This assumes the filter should be in the first filter group (0). 

References:

https://api.drupal.org/api/views/views.api.php/function/hook_views_query_alter/7.x-3.x

https://api.drupal.org/api/views/plugins!views_plugin_query_default.inc/function/views_plugin_query_default%3A%3Aadd_where_expression/7.x-3.x