I'm creating a report based on a rolling 12 months of data. I don't want to summarize data if there isn't at least 12 months of data.
My data table contains 4,000 records with fields containing a transaction Date and Value and a calculation field that shows the date 12 months prior to the Date field.
A self join relationship finds all of the records within the previous 12 months of the Date.
I'd like to know how many months of related data I have.
For example, assuming my transaction date is 5/1/2016, the start date for my calculation would be 5/1/2015. Assuming I have transactions in each month between these two dates, the result would be 12. If one month had no transactions, the result would be 11.
I'm at a loss as to how to create this calculation.