Converting from quarterly data collection to monthly data collection

[Anonymous]

Hi everyone,

I have two tables, one as a standard date table(DateTable), and a data collection table{OfficeStats) that captures statistics for office buildings in my city (Houston). We have data from 1999 that was provided only on a quarterly basis. 

Within the OfficeStats table, we have these 4 columns:

• BuildingID
• MonthRecorded
• RentableSquareFeet
• VacantSquareFeet

Two months ago,  we started tracking these statistics on a monthly basis. For example the last 12 rows would look like this under MonthRecorded:

• 3/1/2018
• 2/1/2018
• 1/1/2018
• 10/1/2017
• 7/1/2017
• 4/1/2017
• 1/1/2017
• 10/1/2016
• 7/1/2016
• 4/1/2016
• 1/1/2016
• 10/1/2015....and so on

So the functions to compare previous quarters, months or years based upon March 2018 don't work because there's no data for 3/1/2017, or 12/1/2016. I have no issue using the same numbers from 10/1/20XX for 11/1/20XX and 12/1/20XX but don't know a good way to do this. Any ideas?

[Ashish_Mathur]

Hi,

It will be very useful if you show both tables and also your expected result.

[Anonymous]

Hi Everyone - it sounds like a need to do abetter job of explaining what I"m asking about. I'm tracking historical stats for Houston office buildings. One table is the standard date table I created in the advanced editor.

The other table has roughly 50,000 rows of data, table "OfficeStats." The data I have is from 1999 and each period the data is collected creates one new row each building. We track about 700 buildings. This table has the following columns:

• ID: Autonumber
• MonthRecorded:
• RSF(rentable square feet): Whole Number
• Vacant SF: Whole Number
• Sublease SF: Whole Number

That's it - no calculated columns.

So since 1999, the table has entries like this:

PropertyID	MonthRecorded	RSF	Vacant SF	Sublease SF
6459256	1/1/2018	305885	27791	0
234891	1/1/2018	279329	47080	0
235937	1/1/2018	239417	72124	0
239815	1/1/2018	117261	25519	3467
6019272	1/1/2018	327404	30932	118352
239606	1/1/2018	162909	71513	0
239968	1/1/2018	110480	110480	0
240136	1/1/2018	92868	15283	0
240085	1/1/2018	130828	46167	0
240341	1/1/2018	41607	2338	0
239778	1/1/2018	120651	0	0
240343	1/1/2018	25760	4825	0
353570	1/1/2018	155407	100981	0
6453001	1/1/2018	89750	0	0
240143	1/1/2018	64980	44588	0
23558	1/1/2018	35323	3455	0
239632	1/1/2018	183268	92505	0

So for 4 times a year, we have 700 rows of data, that are all saved under MonthRecorded as:

• 1/1/1999
• 4/1/1999
• 7/1/1999
• 10/1/1999
• 1/1/2000
• 4/1/2000
• 7/1/2000
• 10/1/2000
• 1/1/2001
• and so on...

Moving forward, we going to record this information on a monthly basis. For example, if I enterered in data from 3/1/2018 and I try to use a function to look back three months, the value is 0 because there are no records from 12/1/2017.

I'd like to fill in the 8 months that do not have any data - February, March, May, June, August, September, November & December. I'm ok with just copying from the previous month, or previous two months.

But I don't know how to do that. If i use a conditional column, how do I get everything into the final format with just one singular data column?

[Greg_Deckler]

I can think of 2 ways of doing this. One way is that you create a new table which is a NATURALINNERJOIN of your Monthly table (all years, all months) and your Quarterly table. You should end up with a table with all of your Month/Years and values for your months that you have data for. You could then create a new column that essentially is if there is a value, use that value, otherwise, use the last value you have in the table (most recent with respect to that row).

The other way is essentially the same thing in M code (Power Query).

If you need further help, please refer to:

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490