I have a data set consisting of Jobs and Statuses with updated datetime joined as follows:
My goal is to create a bar chart that shows the count of jobs with their latest status TAG based on TransactionTime within the selected date period (max of FullDate). The count should be 14, but if I add the TAG the count is over 14 due to the one-to-many relationship.
Fulldate field in Calendar is linked to Job table via a DateBridge table with Job ID and pending date. In this report, I filter down to one period (3/1 - 3/14), and I only want to count number of Jobs that are still pending by period end. So the =Max(FullDate) works.
The challenge is to also restrict the status to be the latest transaction time that is smaller than the max period date.