cancel
Showing results for
Did you mean:
Regular Visitor

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

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!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 25 members 966 guests
Recent signins: