Prior Period Calculations with Multiple Fiscal Year End's (R Equivalent to LOOKUPVALUE?)

[Rymatt830]

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:

Organization	PropertyID	Months	PeriodNumber	PeriodID	NCF	...
Org1	        abc	        3	20031	        18	        2200	...
Org1	        abc	        6	20032	        19	        4600	...
Org1	        abc	        9	20033	        20	        6700	...
Org1	        abc	        12	20034	        21	        8000	...
Org1	        abc             3	20041	        22	        4000	...
Org1	        abc	        6	20042	        23	        12000	...

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:

ChgNCFfromPP =
IF (
    ISBLANK ( QuarterlyIndicators[NCF] ),
    BLANK (),
    IF (
        QuarterlyIndicators[Months] = 3,
        QuarterlyIndicators[NCF],
        IF (
            ISBLANK (
                LOOKUPVALUE (
                    QuarterlyIndicators[NCF],
                    QuarterlyIndicators[PropertyID], QuarterlyIndicators[PropertyID],
                    QuarterlyIndicators[PeriodID], QuarterlyIndicators[PeriodID] - 1
                )
            ),
            BLANK (),
            QuarterlyIndicators[NCF]
                - LOOKUPVALUE (
                    QuarterlyIndicators[NCF],
                    QuarterlyIndicators[PropertyID], QuarterlyIndicators[PropertyID],
                    QuarterlyIndicators[PeriodID], QuarterlyIndicators[PeriodID] - 1
                )
        )
    )
)

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).

Here's the output:

Organization	PropertyID   Months   PeriodNumber   PeriodID   NCF	 ChgNCFfromPP  FiscalYear
Org1	        abc	     3	      20031	     18	        2200	 2200          2003
Org1	        abc	     6	      20032	     19	        4600	 2400          2003
Org1	        abc	     9	      20033	     20	        6700	 2100          2004
Org1	        abc	     12	      20034	     21	        8000	 1300          2004
Org1	        abc	     3	      20041	     22	        4000	 4000          2004
Org1	        abc	     6	      20042	     23	        12000	 8000          2004

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!