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

[ctaylor]

Hello! 

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.

I work at a property management company and I need to track/plot the unit statuses by property through time. 

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.

What is happening when I try to plot this out is that my line graph is showing spikes in values (image 2).  This is happening because it's only plotting points on days when status changes happen.  What I want it to do is to track the last known value at any given time of the units.  Example: A property has 50 units, if nobody moves out that line should stay a flat 50.  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.

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.

Thanks!

[ctaylor]

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]

hi, @ctaylor 

Do you mean that 

AFTER = CALCULATE(COUNTA(Table1[Q]),ALLSELECTED(Table1[Date]))

or

cumulative = CALCULATE(COUNTA(Table1[Q]),FILTER(ALLSELECTED(Table1),Table1[Date]<=MAX(Table1[Date])))

For example:

if not your case, please share a simple sample data with your expected output.

Best Regards,

Lin

[ctaylor]

@v-lili6-msft thank you for taking the time to look at this and for your reply.

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. 

Refering back to my original post, I am looking to get the last known status of each unit through time.  

Below is a quick summary table of unit_status, filtered by one single unit and ordered by date asc.

If I was just graphing the status of just this one unit I am looking to see lines tracking the latest sStatus that is <= each date that falls within the date filter range.

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".  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.

This same concept would then obviously apply to the entire portfolio.  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.

Thanks!

[v-lili6-msft]

hi, @ctaylor 

For  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 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

Best Regards,

Lin

[ctaylor]

@v-lili6-msft Thanks again for the reply.

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.  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.

Also, I am not sure how to give you a sample graph because, it's the crux of my problem here anyway.  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.  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.  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.

Date	Unit	Status
1/1/2018 0:00	1	Occupied
1/9/2018 0:00	1	Vacant
1/21/2018 0:00	1	Occupied

[v-lili6-msft]

hi, @ctaylor 

It may be difficult to achieve, And if you could try this way:

Add two measure

Occupied measure = CALCULATE(COUNTA('Table'[Unit]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])),'Table'[Status]="Occupied")-CALCULATE(COUNTA('Table'[Unit]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])),'Table'[Status]="Vacant")

Vacant Measure = CALCULATE(DISTINCTCOUNT('Table'[Unit]),ALLSELECTED('Date'))-[Occupied measure]

Result:

Best Regards,

Lin

[ctaylor]

@v-lili6-msft thank you so much for your persistance in helping me figure this out!

So, I applied the two formulas you gave to the same data, but I get something slightly different for some reason.

Here is my code with the actual table names/columns in it, i checked over them and they should match your pseudo-code

Occupied Measure = CALCULATE(COUNTA(sample_table[Unit]),FILTER(ALLSELECTED(DateKey),DateKey[Date]<=MAX(DateKey[Date])),sample_table[Status]="Occupied")-CALCULATE(COUNTA(sample_table[Unit]),FILTER(ALLSELECTED(DateKey),DateKey[Date]<=MAX(DateKey[Date])),sample_table[Status]="Vacant")

Vacant Measure = CALCULATE(DISTINCTCOUNT(sample_table[Unit]),ALLSELECTED(DateKey[Date]))-[Occupied Measure]

The lines start out fine, but then the occupied flips negative and the vacants show 2...which means something is being double counted.  What am I missing?

Again, thanks for your continued help!