cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rymatt830 Regular Visitor
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!

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 25 members 966 guests
Please welcome our newest community members: