I wrote a Calculated Column that returns the change in a value compared to the prior period, while ensuring a few conditions are met. The table contains 75,330 records of property management data for approximately 100 different nonprofit organizations submitted over 71 reporting periods. I didn't use any Time Intelligence functions because there are eleven different fiscal year-end's and I didn't know where to begin. The table has 18 total columns; but, here's the general structure:
As you can see, the data are reported cumulatively (3 months this quarter, 6 months next quarter, etc.), and I needed to calculate the change from the prior-period so all of the records represent just three-months of property management history. Unfortunately, the reporting window is based on the calendar year, not each organization's fiscal year-end (i.e. 3-months reported on 3/31, 6-months on 6/30, etc.). The PeriodID column ranges from 1 to 71, and it's simply an index column based on the unique values of PeriodNumber sorted in ascending order. PeriodNumber is of the format, YYYYQtr (so Q1 2004 is 20041). As I mentioned, these 100 organizations share 11 different fiscal year-end's, and my data goes back to 2001. Here's the Calculated Column:
First, if the reported NCF in the current period or the priod period is blank, a blank is returned. Second, if the number of months reported = 3 in the current period, the current period's NCF is returned (because the goal was to get 3-months of data for each period).
I can now group by Organization and FiscalYear, and the values for NCF - and any other variables in which I apply the same formula - will represent 12-months of reporting history.
I searched all over the place for a way to deal with multiple fiscal year-end's, and I didn't have any luck. So, I'm sharing this to hopefully save someone else some time in the future. Or, suggest a way it can be improved!