Creating and plotting aggregate value by last known entity status, filtered by date

Question

Hello!&nbsp;I have been trying to find an answer to an issue I have, but so far have not found it....so I made an account to ask. I am a little bit beyond a novice but there is still so much I am looking forward to learn in PowerBI.&nbsp;I work at a property management company and I need to track/plot the unit statuses by property through time.&nbsp;I created my date table (DateKey), which I am using for relative date filtering.In our database we have a table that tracks all status changes to units. We manage roughly 11,000 units so this table is rather large. Below is a sample of the table.&nbsp;What is happening when I try to plot this out is that my line graph is showing spikes in values (image 2).&nbsp; This is happening because it's only plotting points on days when status changes happen.&nbsp; What I want it to do is to track the last known value at any given time of the units.&nbsp; Example: A property has 50 units, if nobody moves out that line should stay a flat 50.&nbsp; If someone moves out then the line should dip to 49 and a new line (Vacant) should rise from 0 to 1 on the date that the change occurs.&nbsp;What is the best way to capture the most recent status value of each unit by property, using the date table as x-axis to allow the line graph to display values based on the overall unit count and not just spikes of activity like it is now.&nbsp;Thanks!&nbsp;&nbsp;

ctaylor · Answer

If it helps, an example of some SQL that will somewhat do what I am looking for is as follows:

declare @myDate datetime = '2017-3-1'

select us.hUnit,us.sStatus,@myDate from unit_status us
left outer join tenant t on t.HMYPERSON = us.hTent
where t.HPROPERTY = 1 /*arbitrarily selecting one of our properties*/
and (dtStart<=@myDate and ISNULL(dtEnd,GETDATE())>=@myDate)
order by us.hunit,dtStart desc

results: 50 rows of data (matching the number of units)

v-lili6-msft · Answer

hi,&nbsp;ctaylor&nbsp;
Do you mean that&nbsp;
AFTER = CALCULATE(COUNTA(Table1[Q]),ALLSELECTED(Table1[Date]))
or
cumulative = CALCULATE(COUNTA(Table1[Q]),FILTER(ALLSELECTED(Table1),Table1[Date]&lt;=MAX(Table1[Date])))
For example:

if not your case, please share a simple sample data with your expected output.
&nbsp;
Best Regards,
Lin
&nbsp;

ctaylor · Answer

v-lili6-msft&nbsp;thank you for taking the time to look at this and for your reply.&nbsp;What I am looking for is more in line with the Cumulative measure, but its still not exactly correct in what I am looking to acheive.&nbsp;&nbsp;Refering back to my original post, I am looking to get the last known status of each unit through time.&nbsp;&nbsp;Below is a quick summary table of unit_status, filtered by one single unit and ordered by date asc.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If I was just graphing the status of just this one unit I am looking to see lines tracking the latest sStatus that is &lt;= each date that falls within the date filter range.&nbsp;I would expect to see a line begin at a value of 1 on 2/21/2012 until 6/29/2015 when the unit status changed to "Notice Unrented".&nbsp; At this point the "Occupied No Notice" line would go from 1 to 0 and a line for "Notice Unrented" would go from 0 to 1 until 7/15/2015 when it would then go back down to 0 and a line for "Vacant Rented Not Ready" would then go from 0 to 1.&nbsp;This same concept would then obviously apply to the entire portfolio.&nbsp; So, I am always looking for the sum value of each status category, by latest status relative to the date filter, of all of the units to graph a continuous lines across the entire date range of the date filter.&nbsp;Thanks!

v-lili6-msft · Answer

hi,&nbsp;ctaylor&nbsp;
For&nbsp;&nbsp;2/21/2012, there are two rows of data but why the line begins at a value of 1.
what is the logic to distinguish the&nbsp;sStatus?
Could you share your some simple data with the expected output?
I can't see it clearly from the screenshot, it's better you could paint a simple line chart with your expected output.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
&nbsp;
Best Regards,
Lin

ctaylor · Answer

v-lili6-msft&nbsp;Thanks again for the reply.&nbsp;The reason that there can be multiple lines per day for a unit comes down to human error on the end of the leasing agent processing a change.&nbsp; But we would consider the last sStatus value by day the value to plot as we are working with 1-day old data each day.&nbsp;Also, I am not sure how to give you a sample graph because, it's the crux of my problem here anyway.&nbsp; So, here's a sample table for a fake unit and theoretical status changes and a crudely drawn paint image of what I would expect it to show. A straight line from 1/1 until 1/9 showing a value of 1 for Occupied.&nbsp; Then the Occupied value going to 0 as the status changes to Vacant, with the line for Vacant going from 0 to 1 until the next status change on 1/21.&nbsp; Currently when I try to plot this with real data I get spikes coresponding to the date that the change occurs instead of flat(ish) lines representing the cumulative values of statuses on any given date.&nbsp;DateUnitStatus1/1/2018 0:001Occupied1/9/2018 0:001Vacant1/21/2018 0:001Occupied&nbsp;

Forum Discussion

Creating and plotting aggregate value by last known entity status, filtered by date

7 Replies

Related content

creating an aggregated table

How to create drill-down matrix hierarchy with pre-aggregated data without double-counting?

Data Aggregation issue

problem while plotting Scatter plot

Create line chart without aggregation