Capture Max Date for Report based on Filtered Date

[BHinote]

I need to find a way to capture the Maximum Date allowed relative to the Overall Date Filter applied to my Report and use this in other Calculated Columns/Measures to condition output relative to the Date Captured.

The Scenario:

I have an Open Order Report that lists Orders based on the Date the Order was Received.  This is handled by the Relationship I created between the Order Table's Received Date Field and my Calendar Table's Date Field, with the Calendar's Date Field being added to the Over All Report Filter Criteria.  Each Order listed contains a Due Date which is to be used in determining if the Order is "Past Due" or "Current" relative to the Maximum Date allowed by the Date Filter Criteria.

Example 1:

If today is 05/13/2020 and I run the Report with the Date Filtered Relative to the Past 1 Month, this would establish a Date Range of 04/14/2020 - 05/13/2020, which makes the last possible date for the report run 05/13/2020.

If one of the Orders listed has a Received Date of 04/25/2020 and a Due Date of 05/11/2020, this Order would be listed with a Status Column set to "Past Due", because the Last Filtered Date is 5/13/2020 and the Due Date is 5/11/2020.

Example 2:

If today is 05/13/2020 and I run the Report with the Date Filtered Relative to the Past 1 Calendar Month, this would establish a Date Range of 04/01/2020 - 04/30/2020, which makes the last possible date for the report run 04/30/2020.

In this case, the same Order with a Received Date of 04/25/2020 and a Due Date of 05/11/2020, would be listed with the Status Column set to "Current", because the Due Date of 5/11/2020 is after the Last Filtered Date of 4/30/2020.

I have tried creating both a Calculated Column and/or Measure relative to the Max Calendar Date Field as well as the Max Order Receiver Date Field.  Both have not produced the desired result with one showing the same as the Order Received Date and the other showing the last date of the given month.  The desired result is to have every record listed basing the result off the Last Date relative to the Date Filter Applied.

Thank you in advance for any helpful suggestions provided.

[mahoneypat]

Try this approach to make a calculated column on the open orders table

Status = var lastfiltereddate = Max(Date[Date]) // assuming this is how you get max date for comparison

return if(Table[DueDate]>lastfiltereddate, "Current", "Past Due")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[BHinote]

Thank you for your reply, but unfortunately, this did not capture the Last Date from the Calendar Table, relative to the Filter being Applied to the Report.  It is capturing the Last Date in the Overall Calendar Table. (My Report is Filtered by the Calendar[date] field, which has a Relationship to the Order[ReceivedDate] Field.  The Calendar[date] field is only applied to the Report Filter and the Order[ReceivedDate] is used in the Order Record output.)

[mahoneypat]

Ok. If you are trying to access the max date from a filter on the report, that can't be done from a calculated column.  Is there some other logic to get to the same date?  Calculation relative to Today()?  Last refresh date of the report?

If not, here is a measure version of that logic.

Status = var lastfiltereddate = Max(Date[Date]) // assuming this is how you get max date for comparison

return if(min(Table[DueDate])>lastfiltereddate, "Current", "Past Due")

This assume you have the order # in the visual and there is only one row for each order.  If you need to get a count of open ones (w/o the order # in visual), that is doable too.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[BHinote]

Yes, I am trying to access the Max Date from the filter of the report and the Order# is unique in the Order Table.

Using the logic as a measure did not work either and for some reason produced a result that had mixed values.

The "...FilteredDateAsMeasure" Column should all be "12/31/2019" based on the Overall Date Filter Criteria.

Because the Report is Dynamic allowing the user to Change the Date Filter and produce a different result, Today() and Last Refresh Date cannot be used.  The result must be relative to Cutoff Date (i.e. Last Date) of the Date Filter criteria. (i.e. Run the report with a Cutoff Date of 05/10/2020 and the Orders with a Due Date of 05/12/2020 would still be Current.  Run the report with the Cuttoff of 05/13/2020 and the same Order would now be Past Due.)