Forum Discussion
Converting from quarterly data collection to monthly data collection
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?
3 Replies
- Greg_Deckler
Community Champion
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
- Ashish_Mathur
Super User
Hi,
It will be very useful if you show both tables and also your expected result.
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?
