Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Two months ago, we started tracking these statistics on a monthly basis. For example the last 12 rows would look like this under MonthRecorded:
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?
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:
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:
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |