I've been struggling with this overnight so thought its time to reach out.
I am working with 3 tables:
1. Asset information
Asset ID, IN Service Date, End Service Date
2. Transactional information such as
Asset ID, Month, Production, Receipts (by month)
Month, Total Production, Total Receipts (for all assets).
No trouble with YTD and total year figures by creating relationships between Transactions Table and Summary (based on Month, Category, Year)
What I can't work out, is how to exclude those figures in the Transaction table, where 'Month' is >= End Service Date. There should be no information for these assets but i prefer to do it this way if possible.
I've tried all sorts of combinations in the table relationships (Month >= IN Service Date, Month <= End Service Date) and lots of others but not getting the required result. I am able to effectively hide the results in my Portal using something like:
and Assets::In Service Date<=Transaction Data::Month
and Assets::End Service Date>=Transaction Data::Month
Any ideas much appreciated. I'm starting to think I need to implement the date validity in calculation rather than table relationships?